How to insert multiple rows with a SELECT INSERT

Question

I'm converting data from a table that contains a column used to store multiple strings to a new table that will have a row for each string.

Where the Format is a lookup table and Data is a mapping table.

Old Format

DataId FormatId
  1       1

FormatID    Name
   1      'A B C D'

New Format

 DataId        FormatId
    1             1
    1             2
    1             3
    1             4

 FormatID        Name
    1            'A'
    2            'B'
    3            'C'
    4            'D'

I'm looking for the 'best' way to do this.

In the old format, how are the values delimited? With spaces? — Brent D
– Brent D, Commented Jul 6, 2015 at 18:51
@BrentD Yes. I already have the lookup info inserted as separate rows, I just need to map them to the correct ID's and insert them into the mapping table — user3953989
– user3953989, Commented Jul 6, 2015 at 18:54

M.Ali · Accepted Answer · 2015-07-06 19:10:05Z

Use the following Split function:

Split Function

CREATE FUNCTION Split  
(  
  @delimited nvarchar(max),  
  @delimiter nvarchar(100)  
) RETURNS @t TABLE  
(  
-- Id column can be commented out, not required for sql splitting string  
  id int identity(1,1), -- I use this column for numbering splitted parts  
  val nvarchar(max)  
)  
AS  
BEGIN  
  declare @xml xml  
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'  

  insert into @t(val)  
  select  
    r.value('.','varchar(max)') as item  
  from @xml.nodes('//root/r') as records(r)  

  RETURN  
END

Query

I have created some test data and the query to get all the data you need. use this query to insert data into your new tables.

DECLARE @Original_Table1 TABLE (DataID INT , FormatID INT)
INSERT INTO @Original_Table1 Values (1 ,1),(1 ,2)

DECLARE @Original_Table2 TABLE (FormatID INT , Name VARCHAR(100))
INSERT INTO @Original_Table2 Values (1 ,'A B C D')
                                    ,(2 ,'E F G H')

-- INSERT INTO NewTable bla bla.....    
SELECT   t1.DataID
        ,t2.FormatID AS FormatID_OLD
        ,c.id AS FormatID_new 
        ,t2.Name AS Name_Old
       ,c.val AS Name
FROM @Original_Table2 t2 
                 CROSS APPLY dbo.Split(t2.Name , ' ')c
INNER JOIN @Original_Table1 t1 ON t2.FormatID = t1.FormatID

Result Set

╔════════╦══════════════╦══════════════╦══════════╦══════╗
║ DataID ║ FormatID_OLD ║ FormatID_new ║ Name_Old ║ Name ║
╠════════╬══════════════╬══════════════╬══════════╬══════╣
║      1 ║            1 ║            1 ║ A B C D  ║ A    ║
║      1 ║            1 ║            2 ║ A B C D  ║ B    ║
║      1 ║            1 ║            3 ║ A B C D  ║ C    ║
║      1 ║            1 ║            4 ║ A B C D  ║ D    ║
║      1 ║            2 ║            1 ║ E F G H  ║ E    ║
║      1 ║            2 ║            2 ║ E F G H  ║ F    ║
║      1 ║            2 ║            3 ║ E F G H  ║ G    ║
║      1 ║            2 ║            4 ║ E F G H  ║ H    ║
╚════════╩══════════════╩══════════════╩══════════╩══════╝

Collectives™ on Stack Overflow

How to insert multiple rows with a SELECT INSERT

1 Answer 1

Split Function

Query

Result Set

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Split Function

Query

Result Set

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related