insert separated comma values into sql table

Question

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?

you could use a function returning a table containing integers e.g. [F_SplitAsIntTable](http://stackoverflow.com/a/16993267/1699210) — bummi, Jun 24 '13 at 15:39
Do you have a scripting language you can use to prepare a proper CSV file or SQL statements from this data? — tadman, Jun 24 '13 at 15:39
Below link might be helpful. http://stackoverflow.com/questions/2182668/how-to-split-comma-separated-text-in-mysql-stored-procedure — Ashish Mulaye, Jun 24 '13 at 16:23
I think one of your tags is wrong. sql-server is abbreviation from Microsoft SQL Server. So please delete `mysql` or `sql-server` tag. — Kamil, Jun 24 '13 at 18:06

sion_corn · Answer 1 · 2013-06-24T16:32:39.767

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

Then create a staging table: 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.

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.

CREATE TABLE MyTable ( CustomerID varchar(5) )

SET QUOTED_IDENTIFIER OFF

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

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

Thanks for the solution, but I do not want to store values in CSV file. — Vikrant Shitole, Jul 02 '13 at 16:51

score 1 · Answer 2 · answered Jun 25 '13 at 05:19

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

score 1 · Accepted Answer · answered Jun 27 '13 at 16:51

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

    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

insert separated comma values into sql table

3 Answers3