Posted By: tpower
My database contains three tables called
Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.
I want to copy the data from
DATA_TABLE where it is linked to one given object identity and insert corresponding records into
Link_Table for a different given object identity.
I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.
Is this the best way to do it?
Edit : I want to avoid a loop for two reason, the first is that I’m lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.
I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?
The following sets up the situation I had, using table variables.
DECLARE @Object_Table TABLE ( Id INT NOT NULL PRIMARY KEY ) DECLARE @Link_Table TABLE ( ObjectId INT NOT NULL, DataId INT NOT NULL ) DECLARE @Data_Table TABLE ( Id INT NOT NULL Identity(1,1), Data VARCHAR(50) NOT NULL ) -- create two objects '1' and '2' INSERT INTO @Object_Table (Id) VALUES (1) INSERT INTO @Object_Table (Id) VALUES (2) -- create some data INSERT INTO @Data_Table (Data) VALUES ('Data One') INSERT INTO @Data_Table (Data) VALUES ('Data Two') -- link all data to first object INSERT INTO @Link_Table (ObjectId, DataId) SELECT Objects.Id, Data.Id FROM @Object_Table AS Objects, @Data_Table AS Data WHERE Objects.Id = 1
Thanks to another answer that pointed me towards the OUTPUT clause I can demonstrate a solution:
-- now I want to copy the data from from object 1 to object 2 without looping INSERT INTO @Data_Table (Data) OUTPUT 2, INSERTED.Id INTO @Link_Table (ObjectId, DataId) SELECT Data.Data FROM @Data_Table AS Data INNER JOIN @Link_Table AS Link ON Data.Id = Link.DataId INNER JOIN @Object_Table AS Objects ON Link.ObjectId = Objects.Id WHERE Objects.Id = 1
It turns out however that it is not that simple in real life because of the following error
the OUTPUT INTO clause cannot be on
either side of a (primary key, foreign
I can still
OUTPUT INTO a temp table and then finish with normal insert. So I can avoid my loop but I cannot avoid the temp table.