How to INSERT an array of values in SQL Server 2005?

Question

How do I write the SQL code to INSERT (or UPDATE) an array of values (with probably an attendant array of fieldnames, or with a matrix with them both) without simple iteration?

Answer 1

I construct the list as an xml string and pass it to the stored procs. In SQL 2005, it has enhanced xml functionalities to parse the xml and do a bulk insert.

check this post: Passing lists to SQL Server 2005 with XML Parameters

Answer 2

I understand that you are talking about writing stored procedure to accept array of values

With SQL Server 2005 you would need to use XML variable

SQL 2008 adds support to table variable as parameters

Here you can find good examples of passing a table to a stored procedure as XML and as table variable (SQL Server 2008)

Answer 3

If your data is already in the database you could use INSERT SELECT syntax. It's slightly different from INSERT VALUES one...

INSERT recipient_table (field1, field2)
SELECT field1_from, field2_from
FROM donor_table
WHERE field1_from = 'condition'

Answer 4

Simple way to concatenate the values into a list and pass it to the sp.

In the sp use dbo.Split udf to convert back to resultset (table).

Create this function:

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
returns @Results TABLE (Items nvarchar(4000))
as
begin
declare @index int
declare @slice nvarchar(4000)

select @index = 1
if @String is null return

while @index != 0

begin
select @index = charindex(@Delimiter,@String)
if @index !=0
select @slice = left(@String,@index - 1)
else
select @slice = @String

insert into @Results(Items) values(@slice)
select @String = right(@String,len(@String) - @index)
if len(@String) = 0 break
end return
end

and then try:

select * from dbo.split('a,b,c,d,e,f,g,h,i,j,k,l', ',')