CODEWITHSUNDEEP
sqlsql-server-2005t-sqlinsert
Tim Schmelter
Tim Schmelter

Reputation: 460148

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:

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

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.

Upvotes: 35

Views: 154237

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239704

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.

Upvotes: 14

André Morujão
André Morujão

Reputation: 7133

Something like this should work:

INSERT INTO #IMEIS (imei) VALUES ('val1'), ('val2'), ...

UPDATE:

Apparently this syntax is only available starting on SQL Server 2008.

Upvotes: 61

rahularyansharma
rahularyansharma

Reputation: 10765

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

Upvotes: 11

Related Questions