将分隔的逗号值插入sql表

I have input values in the format of string (separated with comma).

我有字符串格式的输入值(用逗号分隔)。

customerID = "1,2,3,4,5"

How I can insert these value into the column cutomerID of temp customer table?

如何将这些值插入临时客户表的列cutomerID?

3 个解决方案

#1

One way to accomplish this would be to save the values you want to insert as a CSV.

实现此目的的一种方法是将要插入的值保存为CSV。

Then create a staging table: MyTable

然后创建一个临时表:MyTable

Use the following TSQL to bulk insert the contents of your CSV. Make sure to change the file path. Finally, run the commented-out select statement to verify that your import was successful.

使用以下TSQL批量插入CSV的内容。确保更改文件路径。最后,运行注释掉的select语句以验证导入是否成功。

Note that the FIRSTROW argument specifies the number of the first row to load. The default is the first row in the specified data file. This value may need to be changed to fit the layout of your CSV.

请注意,FIRSTROW参数指定要加载的第一行的编号。默认值是指定数据文件中的第一行。可能需要更改此值以适合CSV的布局。

CREATE TABLE MyTable ( CustomerID varchar(5) )

CREATE TABLE MyTable(CustomerID varchar(5))

SET QUOTED_IDENTIFIER OFF

SET QUOTED_IDENTIFIER关闭

DECLARE @SQL varchar(2000), @path varchar(500)

DECLARE @SQL varchar(2000),@ path varchar(500)

SET @path = 'C:\Users\VikrantShitole\Desktop\Test.csv'

SET @path ='C:\ Users \ VikrantShitole \ Desktop \ Test.csv'
SET @SQL = "BULK INSERT MyTable"
    + " FROM '" +   @path + "'"
    + " WITH ("
    + " FIELDTERMINATOR = ','"
    + " ,ROWTERMINATOR = '\n'"
    + " ,FIRSTROW = 2 "
    + ")"

EXEC(@SQL)
-- SELECT * FROM MyTable

- SELECT * FROM MyTable

#2

Try this one -

试试这个 -

Query:

DECLARE @customerID VARCHAR(20)
SELECT @customerID = '1,2,3,4,5'

SELECT customerID = t.c.value('@s', 'INT')
FROM (
    SELECT field = CAST('<t s = "' + 
          REPLACE(
                 @customerID + ','
               , ','
               , '" /><t s = "') + '" />' AS XML) 
) d
CROSS APPLY field.nodes('/t') t(c)
WHERE t.c.value('@s', 'VARCHAR(5)') != ''

Output:

customerID
-----------
1
2
3
4
5

#3

Thanks Devart. This is one more alternate solution. Thanks all for helping me out.

谢谢Devart。这是另一种替代解决方案。谢谢大家帮帮我。

    DECLARE @customerID varchar(max) = Null ;

  SET @customerID= '1,2,3,4,5'


     DECLARE @tempTble Table (
            customerID varchar(25) NULL);


while len(@customerID ) > 0
begin
  insert into @tempTble (customerID ) values(left(@customerID , charindex(',', @customerID +',')-1))
  set @customerID = stuff(@customerID , 1, charindex(',', @customerID +','), '')
end

select * from @tempTble

#1