How to insert into table by extracting values from string multiple times

Question

I have a string with separator. Now I need to insert these values into table extracting data from it. Each set of data is seperated by '|' and each value is seperated by '~'

EX: @data='col1 ~ 100 ~ 200 | col2 ~ 700 ~ 800 | col3 ~ 180 ~ 800 ' Now I need to insert this value to table t1.

**Table T1**
A             B         C
COL1         100       200
COL2         700       800
COL3         180       800

Answer 1

Have you considered using SSIS for loading the data into the table? If, for example, your string data is in a text-file, you could simply use the built-in Flat File Source-component of SSIS, to specify that '|' should act as a row separator and '~' should act as a column separator.

If you cannot use SSIS, you will have to use a T-SQL loop. Inside the loop, you could use the CHARINDEX and SUBSTRING functions to cut the string apart, and construct SQL INSERT statements on the fly.

Answer 2

Step 1. Create a String_Split function. Please follow this link for various options.

Go
CREATE FUNCTION [dbo].[string_split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END
GO

Step 2. Compose the INSERT queries from your data as below. I have used a temp table here, you can easily replace it with your actual table. Please note I did not replace spaces from your @data (you can adjust this SQL as per your needs).

go
    CREATE TABLE #target_table (A VARCHAR(100), B INT, C INT);

DECLARE @temp TABLE (q varchar(max));
DECLARE @data varchar(max)='col1 ~ 100 ~ 200 | col2 ~ 700 ~ 800 | col3 ~ 180 ~ 800' ;
DECLARE @inserts varchar(max)='';
INSERT INTO @temp 
SELECT 'SELECT ''' + REPLACE(val, '~', ''',''') +'''' from dbo.string_split(@data, '|');

SELECT @inserts = @inserts + CHAR(13) + CHAR(10) + q + ' UNION ALL ' 
FROM @temp

SELECT @inserts =  'INSERT INTO #target_table(A, B, C) ' + @inserts 

SELECT @inserts = SUBSTRING(@inserts, 1, LEN(@inserts) - LEN(' UNION ALL'))

--print(@inserts)
EXEC(@inserts)

SELECT * FROM #target_table

If you do not wish to change the #target_table in the above SQL, you could write a SELECT INTO query at the end.

INSERT INTO <Your_Actual_Target_Table>(cols...)
SELECT cols...
FROM #target_table

Note: Please remember to replace any single quotes within the @data with two single quotes, an error will be produced otherwise.

Answer 3

This is quite a brittle answer (i.e. very tailored to this specific purpose rather) but...

1. Create a split function that splits a string into 3 columns (i.e. splits a string like 'col1~100~200' into a table entry with 3 columns)

   CREATE FUNCTION [dbo].[SplitThreeStringList]
   (
       @List NVARCHAR(MAX),
       @SplitOn NVARCHAR(5)
   )
   RETURNS @RtnTable TABLE
   (
       Id NVARCHAR(MAX),
       A INT,
       B INT
   )
   
   AS
   BEGIN
   
   DECLARE @TempString NVARCHAR(MAX);
   DECLARE @ValueLen int;
   DECLARE @idlen int;
   SET @idlen = LEN(SUBSTRING(@List, 0, CHARINDEX(@SplitOn, @List)));
   SET @TempString = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List)-@idlen-LEN(@SplitOn));
   SET @ValueLen = LEN(SUBSTRING(@TempString,0,CHARINDEX(@SplitOn,@TempString)));
   
   INSERT INTO @RtnTable (Id, A, B)
   
   SELECT    
       Id = SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1),
       A = CONVER(int, SUBSTRING(@TempString,1,@ValueLen)),
       B = CONVERT(int, SUBSTRING(@TempString,@ValueLen+LEN(@SplitOn)+1,LEN(@List)-@idlen-@ValueLen-LEN(@SplitOn)-LEN(@SplitOn)))
   
   RETURN
   END
   

2. Create a standard split string function, e.g.

   CREATE FUNCTION [dbo].[SplitStringList]
   (
       @List NVARCHAR(MAX),
       @SplitOn NVARCHAR(5)
   )
   RETURNS @RtnTable TABLE
   (   
       Id INT IDENTITY(1,1),
       Value NVARCHAR(100)
   )
   AS
   BEGIN
   DECLARE @Count int
   SET @Count = 1
   WHILE (CHARINDEX(@SplitOn,@List)>0)
   
   BEGIN
       INSERT INTO @RtnTable (Value)
   
   SELECT
       Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
   
       SET @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
   Set @Count = @Count + 1
   END
   
   INSERT INTO @RtnTable (Value)
   
   SELECT
       Value = LTRIM(RTRIM(@List))
   
   RETURN
   END
   

3. Use the standard split string function to split your original string, then iterate over the rows of the resulting table using a cursor, calling the SplitThreeStringList function each time:

   DECLARE @Value nvarchar(max)
   
   CREATE TABLE #results(
   Value NVARCHAR(max), 
   A INT,
   B INT)
   
   DECLARE db_cursor CURSOR FOR  
   SELECT Value FROM 
   SplitStringList('col1~100~200|col2~700~300|col3~180~400','|')
   
   OPEN db_cursor   
   FETCH NEXT FROM db_cursor INTO @Value   
   
   WHILE @@FETCH_STATUS = 0   
   BEGIN
   INSERT INTO #results (Value, A, B)  
   SELECT * FROM SplitThreeStringList(@Value, '~')
   
   FETCH NEXT FROM db_cursor INTO @Value
   END   
   
   CLOSE db_cursor   
   DEALLOCATE db_cursor
   

4. SELECT * FROM #results

Answer 4

Since others already answered how to do with string parsing I am going to take a different approach. A big reason for packing multiple values into a string like that is to avoid roundtrips to the DB server which depending on your environment can be costly.

Another approach is to create a UDT such as:

CREATE TYPE [dbo].[myT1UDT] AS TABLE
(
    A UNIQUEIDENTIFIER, B DECIMAL(18,5),C INT
)

Then in your stored procedure you can specify that parameter such as:

CREATE PROCEDURE [dbo].[mySP] 
        @Items  AS dbo.MYT1UDT READONLY
AS
INSERT INTO T1 
SELECT * FROM @Items

If your executing this from .Net you can create a Datatable and pass it in