September 8, 2011 at 2:06 am
Hi There,
I am struggling to find a way to insert into a table that has a field that is dependent on another table. Here is the scenario.
I have some data in a table variable that I want to create rows for in TableA in a set-based way. However, TableA has an additional field that is a foreign key to TableB. The row which must be referenced in TableB doesn't exist at the time of the insert to TableA. So, basically, I want to insert to TableA, creating the required row in TableB on the fly.
e.g.:
INSERT INTO dbo.TableA(F1, F2, F3, F4, F5)
SELECT t.F1, t.F2, t.F3, -- F4 to come from table b
t.F5
FROM @TV t
Is there a way to do this without resorting to processing 1 row from the table variable at a time, creating a row in TableB and putting the SCOPE_IDENTITY of the created row in TableB in a variable and then creating a row in TableA that uses the variables value?
TIA,
Chris
September 8, 2011 at 8:33 am
Confused. Why do you not insert the values into TableB first?
September 8, 2011 at 8:36 am
You need two inserts in a transaction to do this correctly.
First insert the necessary parent rows into TableB (I assume one of the columns of @TV) then do the insert into TableA and commit the transaction.
The probability of survival is inversely proportional to the angle of arrival.
September 8, 2011 at 9:25 am
Thanks for the replies guys.
I need to create multiple rows and yet interleave the rows so that row1 created in table b is referenced by row 1 created in table a.
Here is my code that I think should do the job:
DECLARE @BaseData (PK INT INDENTITY(1, 1), F1 INT, F2 INT, F3 INT, F5 INT)
DECLARE @TV( PK INT INDENTITY(1,1), F4 INT)
INSERT INTO dbo.tableB (F4)
OUTPUT inserted.F4 INTO @TV(F4)
SELECT F4
FROM dbo.TableC
INSERT INTO tableA(F1, F2, F3, F4, F5)
SELECT bd.F1, bd.F2, bd.F3, t.F4, bd.F5
FROM @baseData bd INNER JOIN @TV t ON bd.PK = t.PK
For the sake of brevity I've omitted the statement that populates @BaseData but i think this should do what I wanted.
Thanks
September 8, 2011 at 10:00 am
Let us know if it works. I was thinking that OUTPUT might help here, but couldn't figure out how it would work in one statement. This should be good for you if the joins are correct.
September 8, 2011 at 12:44 pm
Works a treat.
September 9, 2011 at 7:12 am
And if you create a view over the both tables and create an instead-of-trigger on that view. This trigger excutes code alike that was presented. This way you can do a single insert on that view, which is translated by the trigger into inserts into the both tables.
I would also recommend doing a group by on the C4 value (= the key value to be inserted into TableB) in the inserted set of rows to prevent primary key violation errors when someone decides to insert 2 different rows with the same C4 value in it.
September 11, 2011 at 4:11 pm
Chris, I still don't understand your functional requirements. But I've found that creating an instead-of-trigger on a view can be a handy way of hiding from the caller the actual structure the data is stored in. The caller inserts the data using a single insert statement. The trigger takes care of storing the data in the correct way, for example into your 2 tables. Below example demonstrates how to create such a view plus trigger. It is very likely I do not have the correct functionality in the example's trigger code, but the example should still give you an indication of how to do the rest yourself.
create table dbo.TableB (
PK INT IDENTITY(1,1) NOT NULL,
F4 INT NOT NULL,
constraint PK_TABLEB primary key clustered (PK)
);
create table dbo.TableA (
PK INT NOT NULL,
F1 INT NOT NULL,
F2 INT NOT NULL,
F3 INT NOT NULL,
F5 INT NOT NULL,
constraint PK_TABLEA primary key clustered (PK),
constraint FK_TABLEB_TABLEA FOREIGN KEY (PK) REFERENCES dbo.TableB(PK)
);
-- Create a view that returns all required columns from both TableA
-- and TableB. We will not select from it, we will just insert into it.
create view dbo.vTableA_and_B
as
SELECT bd.PK, bd.F1, bd.F2, bd.F3, t.F4, bd.F5
FROM dbo.TableA bd
INNER JOIN dbo.TableB t ON bd.PK = t.PK
go
-- Add an instead-of trigger for inserts into the view.
-- This trigger will be fired whenever an insert is executed
-- against the view. An instead-of trigger has to deal with
-- actually inserting the data into the table(s), SQL server
-- will not modify the table's contents at all when such a
-- trigger is defined.
create trigger tbivTableA_and_B
on dbo.vTableA_and_B
instead of insert
as
begin
declare @TV table (
pk int not null,
f4 int not null,
primary key (pk)
);
-- If no rows were input we can bail out early.
if @@rowcount = 0
return;
-- No duplicate rows affected messages, please.
set nocount on;
-- First insert into TableB, capture the identity
-- values inserted together with the F4 values
-- into @TV.
INSERT INTO dbo.tableB (F4)
OUTPUT inserted.pk, inserted.F4
INTO @TV(pk, F4)
SELECT i.F4
FROM Inserted i
-- Now insert the rows into TableA, using the same
-- identity value assigned for that F4-value in TableB.
INSERT INTO dbo.tableA (pk, F1, F2, F3, F5)
SELECT t.pk, i.F1, i.F2, i.F3, i.F5
FROM Inserted i
left outer join @TV t on (t.F4 = i.F4) -- "left", not "inner" to make sure we do not
-- unwillingly filter some rows.
end
go
-- Demonstrate the caller's single insert with multiple rows.
insert into dbo.vTableA_and_B(F1, F2, F3, F4, F5)
select 1, 2, 3, 4, 5 union all
select 6, 7, 8, 9, 10
-- Show the resulting data in TableA and TableB.
select * from dbo.TableA
select * from dbo.TableB
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy