将子行移至新的父 FK ID？

Question

SQL Server 2008。

我有一个 pk id 1 的父行。在阻止所有其他数据库用户时（这是一个清理操作，因此资源争用不是问题），我想插入一个新行，然后获取所有子行rows 并将其 fk 列更改为新行。以下面的 DDL 为例，我想插入一个新行并为所有 #chi.parid 值赋予值“3”，这样它们现在基本上属于新行，以便可以删除旧行。

帮助！

create table #par ( parid int identity(1,1) , note varchar(8) )
create table #chi ( chiid int identity(1,1) , parid int , thing varchar(8) )
insert into #par values ( 'note1' )
insert into #par values ( 'note2' )
insert into #chi values ( 1 , 'a' )
insert into #chi values ( 1 , 'b' )
insert into #chi values ( 1 , 'c' )

Answer 1

我倾向于避免使用代理键，而倾向于使用自然键或 FK；另外，我会避免使用 IDENTITY 作为人工标识符。老实说，我发现自己属于少数派，并且经常想知道如何使用 IDENTITY FK 实现批量插入。

根据 Alan Barker 的回答，您可以使用 SCOPE_IDENTITY()，但前提是您想要执行此 RBAR（逐行执行）。你说，“这是一个清理操作”，所以也许程序解决方案是可以接受的。

我自己解决这个问题的方法是手动生成一系列潜在的 IDENTITY 值（例如在临时表中），然后使用 SET IDENTITY_INSERT TargetTable ON 强制显然，我需要确保在发生 INSERT 时建议的值实际上不会被使用，因此仍然需要阻止所有其他用户。

有几件事值得关注。有时，IDENTITY 列上的强制性 UNIQUE 约束缺失，因此您可能需要自己检查是否存在冲突。另外，我发现，当值不连续（并且在正范围内！）时，喜欢代理的人可能会有点“慌乱”，或者更糟糕的是，存在依赖于完美的应用程序逻辑序列或已将 IDENTITY 值暴露给业务（在这种情况下，“伪造”企业关键值（例如订单号）可能会落入现实生活中的审计员手中）。

编辑：今天早上阅读另一个SO问题的答案让我想起了SQL Server 2008的OUTPUT子句来捕获表中所有自动生成的IDENTITY值，例如

CREATE TABLE #InsertedBooks
(
 ID INTEGER NOT NULL UNIQUE, -- surrogate
 isbn_13 CHAR(13) NOT NULL UNIQUE -- natural key
);

WITH InsertingBooks (isbn_13)
AS 
(
 SELECT '9781590597453'
 UNION ALL
 SELECT '9780596523060'
 UNION ALL
 SELECT '9780192801425'
)
INSERT INTO Books (isbn_13)
  OUTPUT inserted.ID, inserted.isbn_13   -- <--
  INTO #InsertedBooks (ID, isbn_13)      -- <--
SELECT isbn_13
  FROM InsertingBooks;

INSERT INTO AnotherTable...
SELECT T1.ID, ...
  FROM #InsertedBooks AS T1...;

DROP TABLE #InsertedBooks

Answer 2

我认为您只是想要更新，例如：

UPDATE chi SET parid = 2 WHERE parid = 1

FKeys 不应该成为这里的问题。

Answer 3

除了 Kalium 的解决方案之外，您可以使用 SCOPE_IDENTITY() 函数来检索最后一个表插入的 IDENTITY 值。

begin tran    

   insert into #par values ('New Parent')  

   update #chi set parid= SCOPE_IDENTITY()

   delete from #par where parid = <OLD_ID>    

commit

这样您就可以将其编码为存储过程来完成整个事情：

CREATE PROCEDURE CleanUp @newNote varchar(8), @IDToDelete int 
AS
BEGIN

    BEGIN TRAN

    INSERT INTO #par VALUES (@newNote)      
    UPDATE #chi SET parid= SCOPE_IDENTITY()    
    DELETE FROM #par WHERE parid = @IDToDelete

    COMMIT
END

然后简单地：

exec CleanUp 'Alan',1

Answer 4

那么在这种情况下你可以简单地使用游标。性能不是最好的，但看起来这是一项停机清理工作：

CREATE PROCEDURE CleanUp
AS
BEGIN

    -- BUILD YOUR TEMP TABLE(S) HERE:
    --
    --

    DECLARE @delete_parent_id int;

    -- SELECT ON TEMP TABLE (AS A CURSOR):
    -- Put your specific Select statement here:
    DECLARE delete_cursor CURSOR FOR 
    SELECT parid
    FROM #TEMPTABLE
    WHERE <...> ;


    OPEN delete_cursor;

    BEGIN TRAN
        -- Loop round each selected parent, create new parent, update children and delete old parent.
        FETCH NEXT FROM delete_cursor 
        INTO @delete_parent_id;

        WHILE @@FETCH_STATUS = 0
        BEGIN

            INSERT INTO #par VALUES ('Some new Text') --New Parent Row     
            UPDATE #chi SET parid= SCOPE_IDENTITY() where parid = @delete_parent_id   -- Adjust FK ref on child
            DELETE FROM #par WHERE parid = @delete_parent_id -- delete old parent.

            FETCH NEXT FROM delete_cursor 
            INTO @delete_parent_id;
        END

    COMMIT

    CLOSE delete_cursor;
    DEALLOCATE delete_cursor;
END

Answer 5

感谢大家的意见。看来我“不能”使用 SQL Svr 2008 对此进行基于集合的操作，因此我使用循环进行了 RBAR 解决方案（我认为它的性能比游标更好）。任何可以评论如何使用 try..catch 使其更安全或启发我更多如何在集合中执行此操作的人，请发表评论。 ：）

谢谢。

    Select 
                [parid]
            ,   [name]
    Into    #redo
    From    partable 
    Where   DateDiff( Hour , donewhen ,SysDateTimeOffset() ) > 23
Begin Transaction
Declare @rows int  = ( Select COUNT(*) From #redo )
Declare @parid int 
Create Clustered Index redoix on #redo([parid]) With FillFactor = 100
While @rows > 0
Begin
    Select Top 1 @parid = [parid] from #redo Order By parid Asc

        Insert partable 
            (
                [name]
            )
        Select 
                [name]      
        From #redo 
        Where parid = @parid
        Update chitable
            Set parid = Scope_Identity()
            Where   parid = @parid
        Delete From partable
            Where   parid = @parid

    Delete from #redo where [parid] = @parid 
    Set @rows  = ( Select COUNT(*) From #redo )
End
Commit Transaction