SQL - 将逗号分隔的字符串转换为单独的行后更新表

Question

示例数据

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'

我设法使用下面的查询删除了逗号

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

现在，我想要的是如何用新的一组值更新现有表。谢谢。

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