SQL 从逗号分隔的 varchar-list INSERT INTO TABLE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6354742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT INTO TABLE from comma separated varchar-list
提问by Tim Schmelter
Maybe i'm not seeing the wood for the trees but i'm stuck, so here's the question:
也许我没有看到树木的木材，但我被卡住了，所以这里的问题是：
How can i import/insert a list of comma separated varchar-values into a table? I don't mean something like this:
如何将逗号分隔的 varchar 值列表导入/插入表中？我的意思不是这样的：
'12345678,87654321,11223344'but this:'12345678','87654321','11223344'
'12345678,87654321,11223344'但是这个：'12345678','87654321','11223344'
I have a Split-Function but it seems to be useless in this case, isn't it?
我有一个Split-Function 但在这种情况下它似乎没用，不是吗？
Here is a simple (mock-SQL) example to show what i mean:
这是一个简单的（模拟 SQL）示例来说明我的意思：
Create Table #IMEIS(
imei varchar(15)
)
INTO INTO #IMEIS(imei)
SELECT * FROM ('012251000362843', '012251001084784', '012251001168744', '012273007269862', '012291000080227', '012291000383084', '012291000448515')
SELECT * from #IMEIS
DROP TABLE #IMEIS;
Thank you in advance.
先感谢您。
采纳答案by Damien_The_Unbeliever
Since there's no way to just pass this "comma-separated list of varchars", I assume some other system is generating them. If you can modify your generator slightly, it should be workable. Rather than separating by commas, you separate by union all select, and need to prepend a selectalso to the list. Finally, you need to provide aliases for the table and column in you subselect:
由于没有办法只传递这个“以逗号分隔的 varchars 列表”，我假设其他一些系统正在生成它们。如果你可以稍微修改你的生成器，它应该是可行的。不是用逗号分隔，而是用 , 分隔union all select，并且需要select在列表前加上 a 。最后，您需要为子选择中的表和列提供别名：
Create Table #IMEIS(
imei varchar(15)
)
INSERT INTO #IMEIS(imei)
SELECT * FROM (select '012251000362843' union all select '012251001084784' union all select '012251001168744' union all
select '012273007269862' union all select '012291000080227' union all select '012291000383084' union all
select '012291000448515') t(Col)
SELECT * from #IMEIS
DROP TABLE #IMEIS;
But noting your comment to another answer, about having 5000 entries to add. I believe the 256 tables per selectlimitation may kick in with the above "union all" pattern, so you'll still need to do some splitting of these values into separate statements.
但是请注意您对另一个答案的评论，关于要添加 5000 个条目。我相信每个选择限制256 个表可能会在上述“联合所有”模式中起作用，因此您仍然需要将这些值拆分为单独的语句。
回答by André Moruj?o
Something like this should work:
这样的事情应该工作：
INSERT INTO #IMEIS (imei) VALUES ('val1'), ('val2'), ...
UPDATE:
更新：
Apparently this syntax is only available starting on SQL Server 2008.
显然，此语法仅在 SQL Server 2008 上可用。
回答by rahularyansharma
Sql Server does not (on my knowledge) have in-build Split function. Split function in general on all platforms would have comma-separated string value to be split into individual strings. In sql server, the main objective or necessary of the Split function is to convert a comma-separated string value (‘abc,cde,fgh') into a temp table with each string as rows.
Sql Server 没有（据我所知）内置拆分功能。通常，所有平台上的拆分函数都会将逗号分隔的字符串值拆分为单独的字符串。在 sql server 中，Split 函数的主要目标或必要是将逗号分隔的字符串值 ('abc,cde,fgh') 转换为临时表，每个字符串作为行。
The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.
下面的 Split 函数是表值函数，它可以帮助我们将逗号分隔（或任何其他分隔符值）字符串拆分为单个字符串。
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
select top 10 * from dbo.split('Chennai,Bangalore,Mumbai',',')
从 dbo.split('Chennai,Bangalore,Mumbai',',') 中选择前 10 个 *
the complete can be found at follownig link http://www.logiclabz.com/sql-server/split-function-in-sql-server-to-break-comma-separated-strings-into-table.aspx
