从逗号插入的varchar-list中插入表 [英] INSERT INTO TABLE from comma separated varchar-list

问题描述

也许我没有看到树木的木头，但是我被卡住了，所以这是一个问题:

Maybe i'm not seeing the wood for the trees but i'm stuck, so here's the question:

如何将以逗号分隔的varchar值列表导入/插入到表中? 我的意思不是这样的:

How can i import/insert a list of comma separated varchar-values into a table? I don't mean something like this:

• '12345678,87654321,11223344' 但这:
• '12345678','87654321','11223344'

• '12345678,87654321,11223344' but this:
• '12345678','87654321','11223344'

我有一个Split -Function，但是在这种情况下似乎没用了，不是吗?

I have a Split-Function but it seems to be useless in this case, isn't it?

这是一个简单的(模拟SQL)示例，用于显示我的意思:

Here is a simple (mock-SQL) example to show what i mean:

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;

谢谢.

推荐答案

由于没有办法仅传递此以逗号分隔的varchars列表"，因此我假设有其他系统正在生成它们.如果您可以稍微修改生成器，那么它应该是可行的.不用逗号分隔，而是用union all select分隔，并且还需要在列表前加上select.最后，您需要在子选择中为表和列提供别名:

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 select also to the list. Finally, you need to provide aliases for the table and column in you subselect:

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;

---

但请注意您对另一个答案的评论，关于要添加的5000个条目.我相信每个选择256个表上面的联合所有"模式可能会带来局限性，因此您仍然需要将这些值分成一些单独的语句.

---

But noting your comment to another answer, about having 5000 entries to add. I believe the 256 tables per select limitation may kick in with the above "union all" pattern, so you'll still need to do some splitting of these values into separate statements.

这篇关于从逗号插入的varchar-list中插入表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！