SQL Server - variable stops being assigned inside a loop

Edit 2:

I'm adding the table structures used in this example to try to clarify the issue. Thank you @Sean for your recomendations and your patience.

Create Table TheHeaderTable(
    id       bigint Identity(1,1)   not null,
    varOne   datetime,
    varTwo   nvarchar(30),
    varThree nvarchar(30),
    varFour  nvarchar(6)

    constraint PK_TheTable primary key (id)
)

Create Table TheDetailsTable(
    FK_Bitacora_id bigint, --FK to TheTable.id
    isPK    bit,
    nuevo   varchar(10),
    viejo   varchar(10)

    constraint FK_TheDetailsTable foreign key (FK_Bitacora_id) references TheTable(id)
)

I also changed the table name from "TheTable" to "TheHeaderTable" for clarification.

Original Post

SQL Server 2012

Hi, I have an issue while trying to do a mass insert into one table from another (in another database, same instance).

I basically have 2 databases with the same structure in the same server, and I need to insert the information from 2 database tables (one is a "header" and the other one is the details).

The header has an Identity autoincrementing id, and the database is already being used, so I can't simply copy all the data from one to another, the ID's have to be refreshed. I came with the following code (changed the table and variable names)

declare @id     bigInt   --PK
declare @varOne datetime
declare @varTwo nvarchar(30)
declare @varThree   nvarchar(30)
declare @varFour    nvarchar(6)

declare @outTable table (id bigint)
declare @outId bigint

declare db_cursor CURSOR FOR  
SELECT *
FROM AnotherDatabase.dbo.TheHeaderTable

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id, @varOne, 
@varTwo, @varThree, @varFour

WHILE @@FETCH_STATUS = 0   
BEGIN   
    Insert into TheHeaderTable
    (varOne, varTwo, varThree, varFour)
    Output Inserted.id into @outTable
    VALUES
    (@varOne, @varTwo, @varThree, @varFour)

    select @outId = id from @outTable

    Insert into TheDetailsTable
    (FK_Bitacora_id, nombre, isPK, nuevo, viejo)
    select @outID, nombre, isPK, nuevo, viejo
    from AnotherDatabase.dbo.TheDetailsTable
    where FK_Bitacora_id = @id

    FETCH NEXT FROM db_cursor INTO @id, @varOne, 
        @varTwo, @varThree, @varFour
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

The code seems to work at first, after a few thousand rows the "Details Table" is empty. After investigating for a bit I decided to look at the 1st instance this issue appeared and then I substracted 1 from the ID, and I found out something really weird: ALL the details (from that point onward) were being inserted to that specific ID

I did a query to count the number of inserts by the script (ignoring all possible newly inserted from an external source) and the number of loops before this issue was: 115967

It appears like either @outTable or @outIs is not being correcly updated. But I honestly have no idea why this is happening.

I'd appreciate any help I could get.

Thanks!

EDIT 1:

Just to clarify:

The primary key in "TheHeaderTable" is "id". "TheDetailsTable" is linked with the header table through the foreignKey "FK_Bitacora_id" (referencing id).

ID in TheHeaderTable is autogenerated, This is why I'm capturing its value on insert, but to insert the details, I need to find all rows (in TheDetailsTable) that match with its original id.

Man, I hope I'm clearing things up and not the other way around.

Solution

Since you need to capture the newly inserted identity you can use OUTPUT like you were. But you don't need a loop here. Just do the insert and then join to your other table. All of the code you posted could be simplified to this set based logic. Given the number of rows I would suggest using a temp table instead of a table variable so you can add a clustered index.

create table #outTable
(
    id bigint primary key clustered
)

Insert into TheTable
(
    varOne
    , varTwo
    , varThree
    , varFour
)
Output Inserted.id into #outTable
select varOne
    , varTwo
    , varThree
    , varFour
from AnotherDatabase.dbo.TheHeaderTable

Insert into TheDetailsTable
(
    FK_Bitacora_id
    , nombre
    , isPK
    , nuevo
    , viejo
)
select ot.id
    , nombre
    , isPK
    , nuevo
    , viejo
from AnotherDatabase.dbo.TheDetailsTable d
join #outTable ot on ot.id = d.FK_Bitacora_id

drop table #outTable