Looking for a way to insert to a table that requires a field in another table to be created as part of the same process

  • 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

  • Confused. Why do you not insert the values into TableB first?

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

  • 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

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

  • Works a treat. 🙂

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



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

  • 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



    Posting Data Etiquette - Jeff Moden[/url]
    Posting Performance Based Questions - Gail Shaw[/url]
    Hidden RBAR - Jeff Moden[/url]
    Cross Tabs and Pivots - Jeff Moden[/url]
    Catch-all queries - Gail Shaw[/url]


    If you don't have time to do it right, when will you have time to do it over?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply