INSERT Auto Incremented Primary Key from TableA into FK of TableB

Question

I have two tables: TableA and TableB

CREATE TABLE TableA (
[TableAId] [int] IDENTITY(1,1) NOT NULL...

CREATE TABLE TableB (
[TableBId] [int] IDENTITY(1,1) NOT NULL,
[TableAID] [int] NOT NULL... -- this is the FK

Question: (as a C# developer)

I am writing a function that INSERTs into TableA. I need to grab the newly created primary key in TableA and insert it into the FK of TableB along with other data.

I've done this before, but I didn't like what I did which is lookup the TableA PK value immediately after the insert, store it as a variable and then insert it into TableB.

Can someone show me a more efficient way of doing this? Maybe using scope_identity() in a stored proc? A trigger won't work because I need the new PK back in my C# so I can add additional data before I insert into TableB. Plus, I want to lock both tables while this runs.

Thank you, Robert

Do you want the answer in TSQL or c# - If c# are you using an ORM or raw ADO — Kane, Jun 26 '12 at 04:19
What data access technology are you using? Pure ADO.NET? Entity Framework? Something else? — marc_s, Jun 26 '12 at 04:21
A fair number of major RDBMS providers allow `data change reference`s, which means you can `SELECT` off of only _changed_ (inserted, updated, deleted) rows - what system is your backend on? — Clockwork-Muse, Jun 26 '12 at 04:23

Thomas · Answer 1 · 2012-06-26T04:44:21.640

Declare @TableAId int

Insert TableA ( ... )
Values( ... )

Set @TableAId = Scope_Identity();

Insert TableB( TableAId, ... )
Values( @TableAId, ... )

It should be noted that it is possible to send this to SQL Server in a single batch. I.e., you can send this entire command text to SQL Server and execute all at once. Of course, you'll need to use parameters for all non-identity columns of table A and table B. For example:

const string sql = @"Declare @TableAId int

Insert TableA ( ... )
Values( ... )

Set @TableAId = Scope_Identity();

Insert TableB( TableAId, ... )
Values( @TableAId, ... )";

using ( var conn = new SqlConnection( ... ) )
{
    using ( var cmd = new SqlCommand( sql, conn ) )
    {
        cmd.Parameters.AddWithValue( @TableACol, ... );
        cmd.Parameters.AddWithValue( @TableACol2, ... );
        ...
        cmd.Parameters.AddWithValue( @TableBCol, ... );

        cmd.ExecuteNonQuery();
    }
}

Another choice if you are using SQL Server 2005 or later might be to use the OUTPUT clause:

Insert TableA( ... )
    Output Inserted.TableAId, @TableBCol1, @TableBCol2, ...
    Into TableB
Values( ... )

score 0 · Answer 2 · edited May 23 '17 at 10:34

If you have one stored procedure are inserting both sets of data at the same time then use this code.

DECLARE @id INT

INSERT INTO [dbo].[TableA] VALUES (...)
SET @id = SCOPE_IDENTITY()

INSERT INTO [dbo].[TableB] VALUES (@id ...)

If you are using raw ADO, then set @id as an OUTPUT parameter and use the return value in your second DB call. Refer to here for an example of how to use an output parameter.

As for locking both tables, you can either wrap the call in a transaction (TSQL or ADO.NET) or set the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE for the stored procedure.

score 0 · Answer 3 · edited Jun 26 '12 at 15:16

This worked...

USE [TestDB01] 

GO 

SET ANSI_NULLS ON 

GO 

SET QUOTED_IDENTIFIER ON 

GO

CREATE PROCEDURE [dbo].[sp_INSERT_INTO_Cars_And_Owners]

-- Add the parameters for the stored procedure here

         @PartnerId INT  
        , @Title NVARCHAR(100)  
        , @Description TEXT  
        , @IPAddress NVARCHAR(16)   
        , @IsCarOwner BIT
        , @OwnerTypeTypeID INT

AS 

BEGIN 

-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON; 


DECLARE @CarId INT 


INSERT INTO [dbo].[Cars] ( 
    PartnerId 
    , Title 
    , Description 
    , IPAddress 
    , IsCarOwner 
    ) 
VALUES ( 
    @PartnerId 
    , @Title 
    , @Description 
    , @IPAddress 
    , @IsCarOwner 
    )  


SET @CarId = SCOPE_IDENTITY() 


INSERT INTO [dbo].[Owners] ( 
    OwnerTypeTypeID 
    , CarId 
    ) 

VALUES ( 
    @OwnerTypeTypeID 
    , @CarId 
    )  

END

INSERT Auto Incremented Primary Key from TableA into FK of TableB

3 Answers3