从逗号分隔的 varchar-list INSERT INTO TABLE [英] 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 INSERT INTO TABLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！