Split string then insert new values into new table

July 5, 2023

I am trying to write a migration script to take one column of comma separated values and insert them individually into a new table in SQL server.

The current column is a list of email addresses and I am creating a new table to hold the collection of email addresses for each company.

I would assume I need to first split the string and then run through and INSERT statement for each entry in the array.

Maybe something like this…?

SELECT Id,  value
FROM Companies  
    CROSS APPLY STRING_SPLIT(Email, ',')

INSERT INTO emailAddresses(emailAddress, companyId)
VALUES (value, Id)

But I don’t know how to keep the INSERT statement running for all values.

>Solution :

Use insert...select.
First, create and populate sample table (Please save us this step in your future questions):

CREATE TABLE Companies  (
  Id int identity(1,1),
  Email nvarchar(4000)
);

INSERT INTO Companies(Email) VALUES
('A@gmail.com,B@gmail.com,C@gmail.com'),
('D@gmail.com,E@gmail.com,F@gmail.com'),
('G@gmail.com,H@gmail.com,I@gmail.com');

-- Target table
CREATE TABLE emailAddresses(
  emailAddress nvarchar(1000), 
  companyId int
);

Then, the insert statement:

INSERT INTO emailAddresses(emailAddress, companyId) 
SELECT value, Id
FROM Companies  
CROSS APPLY STRING_SPLIT(Email, ',');

A quick select to see the results:

SELECT * 
FROM emailAddresses

Results:

emailAddress    companyId
A@gmail.com     1
B@gmail.com     1
C@gmail.com     1
D@gmail.com     2
E@gmail.com     2
F@gmail.com     2
G@gmail.com     3
H@gmail.com     3
I@gmail.com     3

See a live demo on db<>fiddle