Split String & Insert Into Table Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Split String & Insert Into Table Rows

Author

Topic

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 14:31:03

I have a string which is delimited by commas:

Green,Red,Blue,Orange,Black,White

I need to create a SQL statement to read the string, split it at the "," character, and insert it into individual rows. I also need to insert an ID (the same for all split values).

For instance:

INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (1, 999, Green)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (2, 999, Red)
INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (3, 999, Blue)
etc...

I would like to be able to do this all in one statement so I don't have to break it out into individual SQL statements.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-04-17 : 14:56:10

If it's just one input string, the simplest thing is to pick it apart using charindex and substring, then use the pieces to insert your values. e.g.


declare @str varchar(1000) = 'Green,Red,Blue,Orange,Black,White'
declare @i int = 1
while len(@str) > 0 begin
    declare @comma int= charindex(',', @str)
    if @comma = 0 set @comma = len(@str)+1
    declare @color varchar(1000) = substring(@str, 1, @comma-1)
    INSERT INTO tbl_Temp (unique_id, ID, color) VALUES (@id, 999, @color)
    set @str = substring(@str, @comma+1, len(@str))
    set @i +=1
end

BLarche
Starting Member

21 Posts

Posted - 2014-04-17 : 15:31:16

This is being run in an ASP application. I will need to run some sort of function or stored procedure to update my DB?

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-18 : 04:02:05

May be something like this......



CREATE FUNCTION String(@Col2 VARCHAR(MAX))
RETURNS   @Result TABLE (Color nvarchar(4000))
AS
BEGIN
  DECLARE @startingposition INT
  DECLARE @InputString VARCHAR(50) = @Col2
  DECLARE @parts nvarchar(4000)
  SELECT @startingposition = 1
WHILE @startingposition !=0
  BEGIN
    SELECT @startingposition = CHARINDEX(',',@InputString)
     IF @startingposition !=0
        SELECT @parts = LEFT(@InputString,@startingposition - 1)
     ELSE
        SELECT @parts = @InputString
        INSERT INTO @Result(color) VALUES(@parts)
        SELECT @InputString = RIGHT(@InputString,LEN(@InputString) - @startingposition)
  END
RETURN;
END
DECLARE @TEMP TABLE (unique_id INT IDENTITY(1,1), ID INT, color VARCHAR(MAX))
INSERT INTO @TEMP(ID,color) SELECT 999 AS ID,color   FROM  dbo.string('Blue,Green,Red,Yello,White')
SELECT * FROM @TEMP

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-18 : 16:10:11

see this article I wrote on split string @ http://sqlsaga.com/sql-server/split-function-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources