Move Child Rows to new Parent FK ID?

SQL Server 2008.

I have a parent row 开发者_如何学JAVAwith pk id 1. While blocking all other DB users (this is a cleanup operation so resource contention is not an issue), I would like to insert a new row, then take all of the child rows and change their fk column to the new row. With the below DDL for example, I would like to insert a new row and give all of the #chi.parid values a value of '3' so they would essentially now belong to the new row so the old one can be deleted.

Help!

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' )

I tend to shun surrogate keys in favour of natural keys or FKs; also, I would avoid IDENTITY for artificial identifiers. To be honest, I find myself in the minority and have often wondered myself how to achieve bulk inserts with IDENTITY FKs.

As per Alan Barker's answer, you can utilize SCOPE_IDENTITY() but only if you want to do this RBAR (row by agonizing row). You say, "this is a cleanup operation" so perhaps a procedural solution is acceptable.

The way I've got around the problem myself is to manually generate a sequence of potential IDENTITY values (e.g. in a staging table) then use SET IDENTITY_INSERT TargetTable ON to force the values in. Obviously, I need to ensure the proposed values will not actually be in use by the time the INSERT occurs so all other users will still need to be blocked.

A couple of things to watch. Sometimes the obligatory UNIQUE constraint on the IDENTITY column is missing so you may need to check there are no collisions yourself. Also, I've found that the kind of person who likes surrogates can get a bit 'flustered' when the values aren't sequential (and in the positive range!) or, much worse, there is application logic that relies on a perfect sequence or has exposed the IDENTITY values to the business (in which case 'faking' enterprise key values such as order numbers can fall fowl of real life auditors).

EDIT: reading an answer to another SO question this morning reminded me about SQL Server 2008's OUTPUT clause to capture all the auto-generated IDENTITY values in a table e.g.

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

I think you just want an update, such as :

UPDATE chi SET parid = 2 WHERE parid = 1

FKeys shouldn't be an issue here.

Further to Kalium's solution, you could use the SCOPE_IDENTITY() function to retrieve the IDENTITY value of the last table insert.

begin tran    

   insert into #par values ('New Parent')  

   update #chi set parid= SCOPE_IDENTITY()

   delete from #par where parid = <OLD_ID>    

commit

This way you could code this as a Stored Procedure to do the whole thing:

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

And then simply:

exec CleanUp 'Alan',1

Well in that case you could simply use a Cursor. Not the best for performance but looks like this is a downtime-clean up job anyway:

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

Thanks all for the input. It appears I "can't" do a set-based operation on this with SQL Svr 2008, so I did RBAR solution with a loop (I think it performs better than a cursor). Anyone who can comment on making this safer with try..catch or enlighten me more on doing this in a set, please comment. :)

Thanks.

    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