用数组“插入" [英] 'insert into' with array

问题描述

我想知道是否有办法在值列表上使用插入".我正在尝试这样做:

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

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

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.

我使用的是 SQL Server 2008 R2.谢谢各位！

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

推荐答案

可以使用这种类型的插入语句

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

'value'、'value3' 和 'abc,def,gjkl' 是您需要的 3 个 varchar 参数在 C# SQLCommand 中设置.

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

这是所需的支持功能.

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

使用的参数是:

1. ',' = 分隔符
2. -1 = 数组中的所有值，或仅前 N 项为 N

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

或者，如果你喜欢，一个纯粹的 CTE 方法，不受任何拆分函数的支持(观看带有 <<< 的评论)

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

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

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

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

这篇关于用数组“插入"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！