'insert into' with array

Question

I'm wondering if there's a way to use 'insert into' on a list of values. I'm trying to do this:

insert into tblMyTable (Col1, Col2, Col3)
     values('value1', value2, 'value3')

So, what I'm trying to say is that value2 will be an array of strings. I'm going to put this in C# but the SQL statement is all I need really. I know I could just use a foreach and loop through my array but I figured there might be a better way sort of like the SELECT statement here: SQL SELECT * FROM XXX WHERE columnName in Array. It seems like a single query would be much more efficient than one at a time.

I'm using SQL Server 2008 R2. Thanks fellas!

Answer 1

You can use this type of insert statement

insert into tblMyTable (Col1, Col2, Col3)
select 'value1', value, 'value3'
from dbo.values2table('abc,def,ghi,jkl',',',-1) V

The 'value', 'value3' and 'abc,def,ghi,jkl' are the 3 varchar parameters you need to set in C# SQLCommand.

This is the supporting function required.

CREATE function dbo.values2table
(
@values varchar(max),
@separator varchar(3),
@limit int -- set to -1 for no limit
) returns @res table (id int identity, [value] varchar(max))
as
begin
declare @value varchar(50)
declare @commapos int, @lastpos int
set @commapos = 0
select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
while @commapos > @lastpos and @limit <> 0
begin
    select @value = substring(@values, @lastpos+1, @commapos-@lastpos-1)
    if @value <> '' begin
        insert into @res select ltrim(rtrim(@value))
        set @limit = @limit-1
    end
    select @lastpos = @commapos, @commapos = charindex(@separator, @values, @lastpos+1)
end
select @value = substring(@values, @lastpos+1, len(@values))
if @value <> '' insert into @res select ltrim(rtrim(@value))
return
end
GO

The parameters used are:

1. ',' = delimiter
2. -1 = all values in the array, or N for just first N items

solution is above, alternatives below

Or, if you fancy, a purely CTE approach not backed by any split function (watch comments with <<<)

;WITH T(value,delim) AS (
     select 'abc,def,ghi', ','   --- <<< plug in the value array and delimiter here
),  CTE(ItemData, Seq, I, J) AS (
    SELECT
        convert(varchar(max),null),
        0,
        CharIndex(delim, value)+1,
        1--case left(value,1) when ' ' then 2 else 1 end
    FROM T
    UNION ALL
    SELECT
        convert(varchar(max), subString(value, J, I-J-1)),
        Seq+1,
        CharIndex(delim, value, I)+1, I
    FROM CTE, T
    WHERE I > 1 AND J > 0
    UNION ALL
    SELECT
        SubString(value, J, 2000),
        Seq+1,
        CharIndex(delim, value, I)+1, 0
    FROM CTE, T
    WHERE I = 1 AND J > 1
)

--- <<< the final insert statement
insert into tblMyTable (Col1, Col2, Col3)
SELECT 'value1', ItemData, 'value3'
FROM CTE
WHERE Seq>0

XML approach

-- take an XML param
declare @xml xml
set @xml = '<root><item>abc</item><item>def</item><item>ghi</item></root>'

insert into tblMyTable (Col1, Col2, Col3)
SELECT 'value1', n.c.value('.','varchar(max)'), 'value3'
FROM @xml.nodes('/root/item') n(c)

-- heck, start with xml string
declare @xmlstr nvarchar(max)
set @xmlstr = '<root><item>abc</item><item>def</item><item>ghi</item></root>'

insert tblMyTable (Col1, Col2, Col3)
SELECT 'value1', n.c.value('.','varchar(max)'), 'value3'
FROM (select convert(xml,@xmlstr) x) y
cross apply y.x.nodes('/root/item') n(c)

In C# code, you would only use 4 lines starting with "insert tblMyTable ..." and parameterize the @xmlstr variable.

Answer 2

Since you're using SQL 2008 and C# your best bet is probably to use a a table valued parameter and then join to it.

This is better than passing a comma delimited string because you don't have to worry about quotes and commas in your values.

update Another option is to use the xml data type.

Pre-SQL 2005 another option is to pass an XML string and using OPENXML. If you use an XMLWriter to create your string it will take care of making sure your xml is valid

Answer 3

-- This table is meant to represent the real table you
-- are using, so when you write this replace this one.
DECLARE @tblMyTable TABLE
(
 Value1 VARCHAR(200)
 , Value2 VARCHAR(200)
 , Value3 VARCHAR(200)
);

-- You didn't say how you were going to get the string
-- array, so I can't do anything cool with that. I'm
-- just going to say we've made a table variable to
-- put those values in. A user-defined table type
-- might be in order here.
DECLARE @StringArray TABLE
(
 Value VARCHAR(200)
);

INSERT INTO @StringArray
VALUES ('Jeremy'), ('snickered'), ('LittleBobbyTables'), ('xkcd Reference');

DECLARE @Value1 VARCHAR(200) = 'This guy --->';
DECLARE @Value3 VARCHAR(200) = ' <--- Rocks!';

-- I want to cross apply the two constant values, so
-- they go into a CTE, which makes them as good as
-- in a table.
WITH VariablesIntoTable AS
(
 SELECT
  @Value1 AS Value1
  , @Value3 AS Value3
)
-- Cross applying the array couples every row in the
-- array (which is in a table variable) with the two
-- variable values.
, WithStringArray AS
(
 SELECT
  VariablesIntoTable.Value1
  , StringArray.Value AS Value2
  , VariablesIntoTable.Value3
 FROM VariablesIntoTable
 CROSS APPLY @StringArray StringArray
)
INSERT INTO @tblMyTable
-- The output clause allows you to see what you just
-- inserted without a separate select.
OUTPUT inserted.Value1, inserted.Value2, inserted.Value3
SELECT
 WithStringArray.Value1
 , WithStringArray.Value2
 , WithStringArray.Value3
FROM WithStringArray