INSERT INTO TABLE from comma separated varchar-list

Question

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      12345678 87654321 11223344   but this   12345678   87654321   11223344     I have a Split-Function but it seems to be useless in this case  isn t it   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    Thank you in advance

User · Accepted Answer

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      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

User · Answer

Something like this should work   INSERT INTO  IMEIS  imei  VALUES   val1      val2          UPDATE   Apparently this syntax is only available starting on SQL Server 2008

User · Answer

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   The below Split function is Table-valued function which would help us splitting comma-separated  or any other delimiter value  string to individual string    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  lt 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  gt 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         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

[sql] INSERT INTO TABLE from comma separated varchar-list

Examples related to sql

Examples related to sql-server-2005

Examples related to tsql

Examples related to insert