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.
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