Generate insert statements after splitting in a stored procedure

Question

I have a stored procedure which accept a parameter of type VARCHAR(MAX). That parameter has string separated by commas like

@test = 'test123,test456,test789';

Based on that parameter, I want to generate multiple insert statements.

I'll use the split function defined in this question: Split string by comma in SQL Server 2008.

Can you show me an example how to generate inserts after splitting a string by commas ?

The logic steps should be like:

@test = 'test123,test456,test789';
split @test
use while or cursor ? (I don't know)
INSERT INTO X values ('test123')
INSERT INTO X values ('test456')
...

score 2 · Accepted Answer · answered Feb 15 '13 at 08:19

DECLARE @id VARCHAR(MAX)

SET @id = 'test123,test456,test789,'

WHILE CHARINDEX(',', @id) > 0 
BEGIN

DECLARE @tmpstr VARCHAR(50)
 SET @tmpstr = SUBSTRING(@id, 1, ( CHARINDEX(',', @id) - 1 ))

INSERT  INTO X

VALUES  ( 
          @tmpstr
        )
SET @id = SUBSTRING(@id, CHARINDEX(',', @id) + 1, LEN(@id))
END

score 1 · Answer 2 · answered Feb 15 '13 at 08:34

Try like below it will help you...

Here you don't need any user defined function.... You just use the below code and get your result...

DECLARE @test NVARCHAR(MAX)
SET @test = 'test123,test456,test789'
SET @test = ',' + @test + ','
DECLARE @Part NVARCHAR(MAX)
DECLARE @INDEX    INT
SET @INDEX = CHARINDEX(',',@test)
DECLARE @EIND INT set @EIND = 0

WHILE(@INDEX != LEN(@test))
BEGIN
    SET  @EIND = ISNULL(((CHARINDEX(',', @test, @INDEX + 1)) - @INDEX - 1), 0)

    INSERT INTO X SELECT (SUBSTRING(@test, (@INDEX  + 1),  @EIND)) -- Insert Comes Here

    SELECT @INDEX = ISNULL(CHARINDEX(',', @test, @INDEX + 1), 0)
END

score 0 · Answer 3 · edited May 23 '17 at 12:10

You can dynamically create a SQL statement on the fly and then run that command.Actually loop it isn't necessary to you. This script provide multiple inserting in one INSERT statement

DECLARE @test nvarchar(100) = 'test123,test456,test789';  
DECLARE @dml nvarchar(max) = N''
SET @dml = 'INSERT [dbo].[test2] VALUES' + '(''' + REPLACE(@test, ',', '''),(''') + ''')'
EXEC sp_executesql @dml

Also you can use option with dynamic management function sys.dm_fts_parser

SELECT FULLTEXTSERVICEPROPERTY ('IsFulltextInstalled')

0 = Full-text is not installed. 1 = Full-text is installed. NULL = Invalid input, or error.

If 0 = Full-text is not installed then this post is necessary to you How to install fulltext on sql server 2008?

DECLARE @test nvarchar(100) = 'test123,test456,test789';
INSERT [dbo].[test2]
SELECT display_term
FROM sys.dm_fts_parser('"' + @test + '"', 1033, NULL, 0)

score -1 · Answer 4 · answered Feb 15 '13 at 08:19

This query will split the string, then loop the result to construct the insert comment.

declare @test varchar(30) = 'test123,test456,test789';

-- retrieve number of comma(s) in @test
declare @i int = len(@test) - len(replace(@test,',','')) + 1
declare @cmd nvarchar(255)

-- loop
while @i >= 0
begin
    select @cmd = 'INSERT INTO X VALUES (''' +  parsename(replace(@test, ',', '.'), @i) + ''');';
    -- execute sql
    exec sp_executesql @cmd
    set @i = @i - 1
end

This will fail if the number if items in the list is more than four. — Mikael Eriksson, Feb 15 '13 at 08:25

Generate insert statements after splitting in a stored procedure

4 Answers4