Inserting Multiple Values Into Table in SQL Server

If you need to insert multiple values into table it can be made with one of these methods:

  • write multiple rows after VALUES clause of INSERT command

I used here a thing called table-value constructor. You can read more about it in the MSDN article Table Value Constructor (Transact-SQL)


Important! The first method can be used in SQL Server 2008 version or higher.

Using Table-Valued Parameters in SQL Server Stored Procedures

When you need to pass a definite number of parameters to a stored procedure it’s quite simple. You should define as much parameters as needed. But when you need to pass a non-definite number of parameters (maybe 5, 7, 10 or even 100) it’s harder to implement.

There are some ways to make it:

  1. convert values to string, for example, separated with comma;
  2. pass as XML;
  3. use a table-valued parameter.
In the first two cases you must serialize and deserialize values. The last methods allows to use a parameter like a standard database table. I’ll use it to implement the stored procedure that save phone numbers of the client.
  • create a table type for Phone Numbers

  • create a stored procedure

Important! We must declare a table-value parameter as READONLY.

Now we can declare a variable with type PhoneNumberType, insert some rows into it and call the stored procedure. I use this approach for unit test.
SaveClientPhoneNumbers unit test
[UPDATE 2016-11-18]
There are some considerations about performance of table-valued parameters. Look at Jeremiah Peschka’s post to get more information Using SQL Server’s Table Valued Parameters

Generating a Comma-Delimited String from Rows in SQL Server

I had to solve the task how to make a string of values from a database table because JQuery UI plugin accepts only a comma-delimited string. I used a XML solution suggested by Anith Sen in his article Concatenating Row Values in Transact-SQL

All you need is to replace text in square brackets with actual names. If you need another separator just change the comma char in the 3rd row to something else.

And finally there’s a screenshot of working example.

Select string from rows

UPDATE 2016-04-13:

Here is another great article about different ways how to make group concatenation and performance comparison

Selecting Rows from Comma-Delimited String in SQL Server

Recently I had to find a solution for the following task: JQuery UI plugin makes a comma-delimited string of values, C# passes this string to SqlCommand object as a parameter, then calls a stored procedure in SQL Server. The stored procedure must filter rows in a big table by these values. One of the solution is to parse a string of values into a table, then join with a big table.

Quick search with Google helps me to find an appropriate solution in Brad Schulz blog (you can read the full article here).

[UPDATE 2017-07-31]
I’ve found an excellent article written by Erland Sommarskog Arrays and Lists in SQL Server with probably all possible solutions.

Here is an extract of the most important part.

Be careful with varchar(2) in first line. It should correspond with the max length of values in your string. As you see in the screenshot below the last value 100 in the string is truncated to 10 (only 2 chars) in the last row.

Select rows from string - bad truncation