SQL Update table & Split one column into multiple rows

Posted on 2011-10-06
Last Modified: 2012-05-12
Please help!, I know how to spilt data from one column (seperated by ',')  and create new multiple rows, but how can I update the table at the same time?


I have a table with a few columns and in one of the columns (Col3) it has Emails seperated by a ',' like:

Col1   Col2              Col3  
----    -----               -----
1       ABC Ltd,
2       Compx Ltd,

I am using the following SQL code to split the emails and create new rows (with other columns retaining the same data):

Select *  FROM dbo.Table1  cross apply dbo.split(Col3,',' )

The SQL output is the following with new cols ID and Data that SQL inserts:

Col1             Col2              Col3                                                   Id              Data
----               -----               -----                                                     -----            -----
London       ABC Ltd,          1      
London       Compx Ltd,              2     
London       ABC Ltd,          1                
London       Compx Ltd,              2      

But how do I Update Col3 with the new values from the Data column?, I have tried:

 update dbo.Table1
 set Col3 = F.Data, Col2 = Col2, Col1 = Col1
 Select *
 FROM dbo.Table1  cross apply dbo.split(Col,',' ) as F
 where =

Any help would be much appreciated!
Question by:Seven0fNine
    LVL 51

    Accepted Solution

    create a temp table first

    select * into #tmp
    (Select *  FROM dbo.Table1  cross apply dbo.split(Col3,',' )) x

    then delete all

    delete from myTable

    insert into myTable select * from #tmp
    LVL 51

    Expert Comment

    (fix for above code) actually this one:

    insert into myTable select * from #tmp
    insert into myTable
    select Col1,Col2, Data from #tmp
    LVL 51

    Expert Comment

    you cannot use update since you are duplicating the number of records...
    say you have 3 records, and after these operations you will have 5 records
    so, you cannot achieve it just by an update statement...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now