Insert from semicolon separated list

Question

I have 2 semicolon separated string passed to a stored procedure along with other variables with simple values. These semicolon seperated values actually needs to be split and inserted as different rows. Both the semicolon seperated values are not interrelated and hence can be inserted together into 2 different fields in any order.The spliting is done by using Collapse | Copy Code select CAST(DATA AS INT) as id from SplitString('30:40:50',':') but i want to combine both these splits such that i could pass them together into single insert statements.

for example, I have string1 '30:40:50' and string2 '23,58,48,60' and some values @id=101,datecreated...

Now i want to insert this values into a table that would look like this,

101 30 23 Jun 25 2013 12:22PM

101 40 58 Jun 25 2013 12:22PM

101 50 48 Jun 25 2013 12:22PM

101 null 60 Jun 25 2013 12:22PM

Any of the string could be longer or smaller or equal.

I am unable to figure out proper way of doing this

Can anyone please help me with this.

This question has been asked hundreds of times. Do a search for comma-separated or CSV and then do the same thing as those except replace `,` with `;`... — Aaron Bertrand, Jun 25 '13 at 12:46
Possible duplicate http://stackoverflow.com/questions/10541290/bulk-insert-from-comma-delimited-string — Werner Henze, Jun 25 '13 at 13:01

Devart · Answer 1 · 2013-06-25T13:07:58.563

Try this one -

Query with LEFT JOIN:

DECLARE 
       @s1 VARCHAR(50)
     , @s2 VARCHAR(50)

SELECT 
       @s1 = '30:40:50'
     , @s2 = '23,58,48,60'

SELECT 
       record_id = 104
     , t2.id1
     , t3.id2
     , dt = CONVERT(VARCHAR(20), GETDATE(), 100)
FROM [master].dbo.spt_values t
LEFT JOIN (
     SELECT 
            id1 = t.c.value('@s', 'INT')
          , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
     FROM (
         SELECT field = CAST('<t s = "' + 
               REPLACE(
                      @s1 + ':'
                    , ':'
                    , '" /><t s = "') + '" />' AS XML) 
     ) d
     CROSS APPLY field.nodes('/t') t(c)
     WHERE t.c.value('@s', 'VARCHAR(5)') != ''
) t2 ON t.number = t2.rn
LEFT JOIN (
     SELECT 
            id2 = t.c.value('@s', 'INT')
          , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
     FROM (
         SELECT field = CAST('<t s = "' + 
               REPLACE(
                      @s2 + ','
                    , ','
                    , '" /><t s = "') + '" />' AS XML) 
     ) d
     CROSS APPLY field.nodes('/t') t(c)
     WHERE t.c.value('@s', 'VARCHAR(5)') != ''
) t3 ON t.number = t3.rn
WHERE (t2.id1 IS NOT NULL OR t3.id2 IS NOT NULL)
   AND t.[type] = 'p'

Query with FULL OUTER JOIN:

SELECT 
       record_id = 104
     , t2.id1
     , t3.id2
     , dt = CONVERT(VARCHAR(20), GETDATE(), 100)
FROM (
     SELECT 
            id1 = t.c.value('@s', 'INT')
          , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
     FROM (
         SELECT field = CAST('<t s = "' + 
               REPLACE(
                      @s1 + ':'
                    , ':'
                    , '" /><t s = "') + '" />' AS XML) 
     ) d
     CROSS APPLY field.nodes('/t') t(c)
     WHERE t.c.value('@s', 'VARCHAR(5)') != ''
) t2
FULL OUTER JOIN (
     SELECT 
            id2 = t.c.value('@s', 'INT')
          , rn = ROW_NUMBER() OVER (ORDER BY 1/0)
     FROM (
         SELECT field = CAST('<t s = "' + 
               REPLACE(
                      @s2 + ','
                    , ','
                    , '" /><t s = "') + '" />' AS XML) 
     ) d
     CROSS APPLY field.nodes('/t') t(c)
     WHERE t.c.value('@s', 'VARCHAR(5)') != ''
) t3 ON t2.rn = t3.rn

Output:

record_id   id1         id2         dt
----------- ----------- ----------- --------------------
104         30          23          Jun 25 2013  3:50PM
104         40          58          Jun 25 2013  3:50PM
104         50          48          Jun 25 2013  3:50PM
104         NULL        60          Jun 25 2013  3:50PM

Found an answer by posting it on http://www.codeproject.com/Questions/611559/SQLplusStatementplusforplusInserting Anyways thanks everyone for ur efforts and reply — shiv, Jun 27 '13 at 07:06

Insert from semicolon separated list

1 Answers1