SQL - Update table after Turning a Comma Separated string into individual rows

Question

Sample Data

CREATE TABLE Testdata
(
 SomeID INT,
 OtherID INT,
 String VARCHAR(MAX)
)
INSERT Testdata SELECT 1,  9, '18,20,22'
INSERT Testdata SELECT 2,  8, '17,19'
INSERT Testdata SELECT 3,  7, '13,19,20'
INSERT Testdata SELECT 4,  6, ''
INSERT Testdata SELECT 9, 11, '1,2,3,4'

I managed to remove the comma using the Query below

Select A.SomeID
,B.*
From [filter].[dbo].[Testdata] A
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>' + replace((Select 
replace(A.String,',','§§Split§§')
as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
as A 

Cross Apply x.nodes('x') AS B(i)
) B

Now, what i wish is how to update the existing table with the new set of values. Thank you.

Answer 1

CREATE TABLE Testdata
    (
     SomeID INT,
     OtherID INT,
     String VARCHAR(MAX)
    )
    INSERT Testdata SELECT 1,  9, '18,20,22'
    INSERT Testdata SELECT 2,  8, '17,19'
    INSERT Testdata SELECT 3,  7, '13,19,20'
    INSERT Testdata SELECT 4,  6, ''
    INSERT Testdata SELECT 9, 11, '1,2,3,4'

    CREATE TABLE NewData
    (
      SomeID INT,
      otherID int,
      String VARCHAR(MAX)
    )

    INSERT INTO  NewData (SomeID,OtherID,String)


    Select A.SomeID,OtherID,
    B.* 
    From [dbo].[Testdata] A
    Cross Apply (
    Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select 
    replace(A.String,',','§§Split§§')
    as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
    as A 

    Cross Apply x.nodes('x') AS B(i)
    ) B


    Delete  from Testdata
    Insert Testdata (SomeID, OtherID , String)

    select SomeID, OtherID , String from NewData

Answer 2

So since (in your comment) you say you want to create a new table that's pretty straight forward with your existing query

Pre SQL Server 2016

CREATE TABLE NewData
(
  SomeID INT,
  String VARCHAR(MAX)
)

INSERT INTO NewData
SELECT A.SomeID,
       B.*
FROM [dbo].[Testdata] A
Cross Apply (
Select RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From  (Select x = Cast('<x>' + replace((Select 
replace(A.String,',','§§Split§§')
as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) 
as A 
Cross Apply x.nodes('x') AS B(i)
) B

SELECT * FROM NewData

Post SQL Server 2016

You can now use STRING_SPLIT to get rid of your large SQL block

CREATE TABLE NewData2
(
  SomeID INT,
  String VARCHAR(MAX)
)

INSERT INTO NewData2
SELECT A.SomeID,
       S.value
FROM [dbo].[Testdata] A
CROSS APPLY STRING_SPLIT (A.String, ',') S

SELECT * FROM NewData2

You wont have any constraints etc, so you need to put these in your CREATE TABLE as you normally would.

Note: You should really change your table definition so you are storing integers now rather than a varchar.