SQL Server Split string then insert new values into new table

s4n0splo 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(121)

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.

sql-server

来源：https://stackoverflow.com/questions/76617825/split-string-then-insert-new-values-into-new-table

1条答案

按热度按时间

ymdaylpp1#

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

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Split string then insert new values into new table

1条答案

相关问题

热门标签

最新问答