sql - 复制具有不同 ID 的记录

Question

我正在处理的应用程序调用这样的 SP:

exec CreateChildRecord @ParentID = 123, @ChildID = 124

SP需要将父记录中除ID外的所有字段复制到子记录中。子记录当前可能存在也可能不存在。

我需要的是类似下面的东西:

UPDATE [Table] AS [Table1] SET (data1, data2) = (
   SELECT [Table2].[data1], [Table2].[data2] 
      FROM [Table] AS [Table2] 
      WHERE [Table2.ID] = @ParentID)
   WHERE [Table1].[ID] = @ChildID
IF @@ROWCOUNT = 0
INSERT INTO [TABLE] (id, data1, data2) 
   (SELECT @ChildID, data1, data2 
      FROM [TABLE] 
      WHERE id = @ParentID)

我已经尝试了上述的各种组合，但都没有效果。谁能帮忙？

Answer 1

如果可以使用merge语句:

merge [Table] as T
using (
    select @ChildID as ID, data1, data2
    from [Table]
    where ID = @ParentID
) as P on P.ID = T.ID
when matched then
    update set
        data1 = P.data1,
        data2 = P.data2
when not matched then
    insert (ID, data1, data2)
    values (P.ID, P.data1, P.data2);

如果不能使用merge:

if exists (select * from [Table] where ID = @ChildID)
    update c set
        data1 = p.data1, 
        data2 = p.data2
    from [Table] as c cross join [Table] as p
    where c.ID = @ChildID and p.ID = @ParentID
else
    insert into [Table] (ID, data1, data2)
    select @ChildID, data1, data2
    from [Table]
    where ID = @ParentID

sql fiddle demo