Insert into Table from Comma Separated Varchar-List

INSERT INTO TABLE from comma separated varchar-list

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.

Insert into Table Variable values from a comma separated list

Using one of the split string functions from here..

declare @List   varchar(25) = '2,3,4';
declare @Delinquencies  table (id int);

;with cte
as
(select * from
[dbo].[SplitStrings_Numbers](@list,',')
)

insert into @Delinquencies(id)
select * from cte

Insert data into a table from comma separated string (T-SQL)

This can be achieved with a Table Value Constructor

Example:

Select *
From (Values (1),(2),(3),(4)) tabA (ColA)

Split comma delimited string and insert to a table (int)

Using the Split() function you have mentioned in comments,

-- Variable holding comma separated values
DECLARE @Var VARCHAR(4000);
SET @Var =  '188,189,190,191,192,193,194'

-- Test Target Table
DECLARE @Target_Table TABLE  (First_ID INT,Second_ID INT,Third_ID INT) 

-- Insert statement
INSERT INTO @Target_Table
SELECT 1, CAST(Items AS INT) , 0 
FROM  dbo.Split(@Var, ',')  

-- Test Select
SELECT * FROM  @Target_Table  

Result Set

╔══════════╦═══════════╦══════════╗
║ First_ID ║ Second_ID ║ Third_ID ║
╠══════════╬═══════════╬══════════╣
║        1 ║       188 ║        0 ║
║        1 ║       189 ║        0 ║
║        1 ║       190 ║        0 ║
║        1 ║       191 ║        0 ║
║        1 ║       192 ║        0 ║
║        1 ║       193 ║        0 ║
║        1 ║       194 ║        0 ║
╚══════════╩═══════════╩══════════╝  

Insert new row into a table for each value in the comma separated list of values passed to a stored procedure

You can try using XML for this task. Following an example:

DECLARE @DepartmentId INT = 123;
DECLARE @str NVARCHAR(100) = 'Student1, Student2, Student3';

DECLARE @t TABLE(xstr XML);
INSERT INTO @t VALUES (CAST('<students><student>' + REPLACE(@str, ', ', '</student><student>') + '</student></students>' AS XML))

SELECT T.r.value('.','varchar(100)') as student, @DepartmentId AS DepartmentId, 'Attended' AS Comments, GETDATE() AS [Date]
FROM @t
OUTER APPLY
xstr.nodes('/students/student') t(r)

Split comma separated string and insert into new table with corresponding PK

try this:

Select t.Id,f.SplitData AS Value from #MyTable t
CROSS APPLY dbo.fnSplitString([Values],',') f

Insert two or more Comma separated values in table using sql

Your fn_Split function needs to return the ordinal position of each value so that the separate lists can be correlated. Below is an example using a derived table subquery. You could also use CTEs or CROSS APPLY.

INSERT INTO #temp (sku,qty) 
SELECT 
      sku.value
    , qty.value
FROM (SELECT ordinal_position, value FROM [dbo].[fn_Split](@sku, ',') AS sku
JOIN (SELECT ordinal_position, value FROM [dbo].[fn_Split](@qty, ',') AS qty ON
    sku.ordinal_position = qty.ordinal_position;

Insert comma delimited ids into a table

the split function is a Table-Valued Function, which means it can be treated as a table, and you can do an INSERT..SELECT

insert into table_name (id,column_2,column_3)
SELECT s.item, column_s_some_value, column_3_some_value
FROM Split(@input_string, ',') s
{JOINS if needed to get other column values}

Passing a varchar full of comma delimited values to a SQL Server IN function

Don't use a function that loops to split a string!, my function below will split a string very fast, with no looping!

Before you use my function, you need to set up a "helper" table, you only need to do this one time per database:

CREATE TABLE Numbers
(Number int  NOT NULL,
    CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED (Number ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @x int
SET @x=0
WHILE @x<8000
BEGIN
    SET @x=@x+1
    INSERT INTO Numbers VALUES (@x)
END

use this function to split your string, which does not loop and is very fast:

CREATE FUNCTION [dbo].[FN_ListToTable]
(
     @SplitOn              char(1)              --REQUIRED, the character to split the @List string on
    ,@List                 varchar(8000)        --REQUIRED, the list to split apart
)
RETURNS
@ParsedList table
(
    ListValue varchar(500)
)
AS
BEGIN

/**
Takes the given @List string and splits it apart based on the given @SplitOn character.
A table is returned, one row per split item, with a column name "ListValue".
This function workes for fixed or variable lenght items.
Empty and null items will not be included in the results set.

Returns a table, one row per item in the list, with a column name "ListValue"

EXAMPLE:
----------
SELECT * FROM dbo.FN_ListToTable(',','1,12,123,1234,54321,6,A,*,|||,,,,B')

    returns:
        ListValue  
        -----------
        1
        12
        123
        1234
        54321
        6
        A
        *
        |||
        B

        (10 row(s) affected)

**/

----------------
--SINGLE QUERY-- --this will not return empty rows
----------------
INSERT INTO @ParsedList
        (ListValue)
    SELECT
        ListValue
        FROM (SELECT
                  LTRIM(RTRIM(SUBSTRING(List2, number+1, CHARINDEX(@SplitOn, List2, number+1)-number - 1))) AS ListValue
                  FROM (
                           SELECT @SplitOn + @List + @SplitOn AS List2
                       ) AS dt
                      INNER JOIN Numbers n ON n.Number < LEN(dt.List2)
                  WHERE SUBSTRING(List2, number, 1) = @SplitOn
             ) dt2
        WHERE ListValue IS NOT NULL AND ListValue!=''

RETURN

END --Function FN_ListToTable

you can use this function as a table in a join:

SELECT
    Col1, COl2, Col3...
    FROM  YourTable
        INNER JOIN FN_ListToTable(',',@YourString) s ON  YourTable.ID = s.ListValue

Here is your example:

Select * from sometable where tableid in(SELECT ListValue FROM dbo.FN_ListToTable(',',@Ids) s)

---

Related Topics