SQL 用数组“插入”

'insert into' with array

提问by snickered

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.

所以，我想说的是 value2 将是一个字符串数组。我将把它放在 C# 中，但 SQL 语句是我真正需要的。我知道我可以只使用 foreach 并循环遍历我的数组，但我认为可能有更好的方法，类似于此处的 SELECT 语句：SQL SELECT * FROM XXX WHERE columnName in Array。一次查询似乎比一次查询效率更高。

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

我正在使用 SQL Server 2008 R2。谢谢各位！

采纳答案by RichardTheKiwi

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.

在'价值'，'值3'和' ABC，DEF，GHI，JKL'是你需要在C＃中的SqlCommand一套3个VARCHAR参数。

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

1. ',' = 分隔符
2. -1 = 数组中的所有值，或者 N 只代表前 N 个项目

solution is above, alternatives below

解决方案在上面，替代方案在下面

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

或者，如果您喜欢，纯 CTE 方法不受任何拆分功能的支持（观看带有 <<< 的评论）

;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

XML 方法

-- 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.

在 C# 代码中，您将只使用 4 行以“insert tblMyTable ...”开头并参数化@xmlstr 变量。

回答by Conrad Frix

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

由于您使用的是 SQL 2008 和 C#，因此最好的办法可能是使用表值参数，然后加入它。

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

这比传递逗号分隔的字符串要好，因为您不必担心值中的引号和逗号。

updateAnother option is to use the xml data type.

update另一种选择是使用xml 数据类型。

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

Pre-SQL 2005 的另一个选项是传递 XML 字符串并使用OPENXML。如果您使用 XMLWriter 创建字符串，它将负责确保您的 xml 有效

回答by Jeremy Pridemore

-- 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