How to Insert Data into Two Tables Using a Single SQL Query

• Use SQL Server 2022 / modern T-SQL terminology.
• Prefer SET XACT_ABORT ON with TRY...CATCH for reliable transaction handling.
• Prefer OUTPUT INSERTED.Id over SCOPE_IDENTITY() when practical.
• Use explicit JOIN syntax instead of comma joins.
• Avoid table variables when a temporary table or direct INSERT...SELECT is more appropriate.
• Demonstrate identity retrieval and multi-row inserts cleanly.
• Include the application/API layer approach commonly used with .NET / EF Core / Dapper.
• Explain when to use a stored procedure versus application-managed transactions.

Here is a much more current version you could use in your documentation.

---

Inserting Data into Multiple SQL Server Tables

When an application needs to insert data into multiple tables, the approach depends on whether the tables are independent or have a relationship such as a primary key / foreign key relationship.

For SQL Server applications, especially modern .NET / ASP.NET Core applications, the preferred approach is to use transactions, parameterized queries, and appropriate key-generation techniques to maintain data consistency.

---

1. Inserting Data into Independent Tables

If the tables are not related, each table can be populated independently.

However, if the inserts are logically part of the same business operation, they should generally be executed inside a transaction.

Example

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO FirstTable
    (
        Column1,
        Column2
    )
    VALUES
    (
        @Column1,
        @Column2
    );

    INSERT INTO SecondTable
    (
        ColumnA,
        ColumnB
    )
    VALUES
    (
        @ColumnA,
        @ColumnB
    );

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

Why use XACT_ABORT and TRY...CATCH?

SET XACT_ABORT ON ensures that certain runtime errors automatically terminate and roll back the transaction.

TRY...CATCH allows the application or calling process to receive the original error using:

THROW;

This is safer than simply using:

BEGIN TRANSACTION
...
COMMIT TRANSACTION

without error handling.

---

2. Inserting into Related Tables

A common scenario is a parent-child relationship.

For example:

Customer
   |
   +---- CustomerAddress
   |
   +---- CustomerOrder

The parent record must normally be inserted first because the child table needs the generated primary key.

For example:

Customer
---------
Id
Name
Email

and:

CustomerOrder
-------------
Id
CustomerId
OrderDate
Amount

Here:

Customer.Id
     ↓
CustomerOrder.CustomerId

---

3. Using OUTPUT INSERTED to Retrieve the Generated ID

In modern SQL Server development, OUTPUT INSERTED.Id is often preferable to SCOPE_IDENTITY() because it works naturally with both single-row and multi-row inserts.

Example

DECLARE @Customer TABLE
(
    Id INT
);

INSERT INTO Customer
(
    Name,
    Email
)
OUTPUT INSERTED.Id INTO @Customer
(
    Id
)
VALUES
(
    @Name,
    @Email
);

DECLARE @CustomerId INT;

SELECT @CustomerId = Id
FROM @Customer;

The generated Customer.Id can then be used when inserting the child record.

INSERT INTO CustomerOrder
(
    CustomerId,
    OrderDate,
    Amount
)
VALUES
(
    @CustomerId,
    GETUTCDATE(),
    @Amount
);

---

4. Complete Parent-Child Transaction

A production-style implementation can look like this:

SET XACT_ABORT ON;

BEGIN TRY

    BEGIN TRANSACTION;

    DECLARE @CustomerId INT;

    DECLARE @NewCustomer TABLE
    (
        Id INT
    );

    INSERT INTO Customer
    (
        Name,
        Email
    )
    OUTPUT INSERTED.Id INTO @NewCustomer(Id)
    VALUES
    (
        @Name,
        @Email
    );

    SELECT @CustomerId = Id
    FROM @NewCustomer;

    INSERT INTO CustomerOrder
    (
        CustomerId,
        OrderDate,
        Amount
    )
    VALUES
    (
        @CustomerId,
        SYSUTCDATETIME(),
        @Amount
    );

    COMMIT TRANSACTION;

END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;

END CATCH;

This guarantees that the operation behaves as one logical unit:

Customer Insert
      ↓
Get Customer ID
      ↓
Order Insert
      ↓
Commit

If the second operation fails, the first insert is rolled back as well.

---

5. Inserting Multiple Child Records

A common real-world requirement is inserting one parent and multiple related child records.

For example:

Order
 │
 ├── Product A
 ├── Product B
 └── Product C

Instead of inserting each record individually, SQL Server can insert multiple rows using a set-based operation.

INSERT INTO OrderItem
(
    OrderId,
    ProductId,
    Quantity
)
SELECT
    @OrderId,
    ProductId,
    Quantity
FROM @OrderItems;

This is generally preferable to repeatedly executing individual INSERT statements.

---

6. Modern Approach Using Table-Valued Parameters

For applications sending multiple records to SQL Server, Table-Valued Parameters (TVPs) can be very useful.

For example:

CREATE TYPE OrderItemType AS TABLE
(
    ProductId INT NOT NULL,
    Quantity INT NOT NULL
);

A stored procedure can then accept the collection:

CREATE PROCEDURE CreateOrder
(
    @CustomerId INT,
    @OrderItems OrderItemType READONLY
)
AS
BEGIN

    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY

        BEGIN TRANSACTION;

        DECLARE @OrderId INT;

        DECLARE @NewOrder TABLE
        (
            Id INT
        );

        INSERT INTO Orders
        (
            CustomerId,
            OrderDate
        )
        OUTPUT INSERTED.Id INTO @NewOrder(Id)
        VALUES
        (
            @CustomerId,
            SYSUTCDATETIME()
        );

        SELECT @OrderId = Id
        FROM @NewOrder;

        INSERT INTO OrderItem
        (
            OrderId,
            ProductId,
            Quantity
        )
        SELECT
            @OrderId,
            ProductId,
            Quantity
        FROM @OrderItems;

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        THROW;

    END CATCH;

END;

This approach is particularly useful when the application needs to send a collection of records to SQL Server in one database operation.

---

7. Linking Existing Data

Sometimes the parent records already exist and the requirement is simply to create relationships between existing records.

For example:

Object
   |
   +---- Link ---- Data

Instead of using an old-style comma join:

FROM @Object_Table AS Objects,
     @Data_Table AS Data

use an explicit JOIN:

INSERT INTO ObjectDataLink
(
    ObjectId,
    DataId
)
SELECT
    O.Id,
    D.Id
FROM ObjectTable AS O
CROSS JOIN DataTable AS D
WHERE O.Id = @ObjectId;

An explicit JOIN makes the relationship much easier to understand and maintain.

---

8. Modern Version of the Object / Link / Data Example

The original example can be simplified and modernized.

DECLARE @ObjectId INT = 1;

DECLARE @Data TABLE
(
    Id INT IDENTITY(1,1),
    DataValue VARCHAR(50) NOT NULL
);

INSERT INTO @Data
(
    DataValue
)
VALUES
    ('Data One'),
    ('Data Two');

INSERT INTO ObjectDataLink
(
    ObjectId,
    DataId
)
SELECT
    @ObjectId,
    Id
FROM @Data;

This uses a multi-row VALUES insert rather than multiple individual inserts.

---

9. Insert and Link a Newly Generated Record

If the new data record needs to be immediately associated with an existing object, OUTPUT can perform the operation cleanly.

DECLARE @NewData TABLE
(
    Id INT
);

INSERT INTO Data
(
    DataValue
)
OUTPUT INSERTED.Id
INTO @NewData(Id)
VALUES
(
    'Data Three'
);

INSERT INTO ObjectDataLink
(
    ObjectId,
    DataId
)
SELECT
    @ObjectId,
    Id
FROM @NewData;

This avoids depending on SCOPE_IDENTITY() and also works well when the insert eventually becomes a multi-row operation.

---

10. Should You Use a Stored Procedure?

A stored procedure is still a good option when:

• Multiple database operations must execute atomically.
• Complex SQL/business rules belong close to the database.
• Multiple applications consume the same database operation.
• You need TVPs or complex set-based processing.
• Database-level security and permissions are important.

For example:

API
 ↓
Service
 ↓
Repository
 ↓
Stored Procedure
 ↓
SQL Server

However, a stored procedure is not automatically required just because multiple tables are involved.

---

11. Modern .NET / EF Core Approach

For a modern ASP.NET Core application using Entity Framework Core, the transaction can instead be managed by the application.

For example:

await using var transaction =
    await dbContext.Database.BeginTransactionAsync();

try
{
    var customer = new Customer
    {
        Name = request.Name,
        Email = request.Email
    };

    dbContext.Customers.Add(customer);

    await dbContext.SaveChangesAsync();

    var order = new Order
    {
        CustomerId = customer.Id,
        OrderDate = DateTime.UtcNow,
        Amount = request.Amount
    };

    dbContext.Orders.Add(order);

    await dbContext.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

EF Core tracks the generated primary key automatically:

customer.Id

after SaveChangesAsync().

This makes the parent-child relationship much simpler at the application level.

---

12. Modern Recommendation

For a modern application, I would generally choose the approach based on the architecture:

Scenario	Recommended approach
Simple independent inserts	Normal parameterized INSERT
Multiple related inserts	Transaction
SQL-heavy business operation	Stored procedure
Multiple child records	Set-based INSERT
Application sends a collection	TVP / bulk operation
ASP.NET Core + EF Core	EF Core transaction
High-performance bulk processing	Bulk insert / set-based SQL
Need generated IDs	OUTPUT INSERTED
Error handling	TRY...CATCH + THROW
Transaction safety	SET XACT_ABORT ON
Multiple applications sharing DB logic	Stored procedure can be appropriate

---

Key Improvements Over the Older Approach

Your original write-up was valid for its time, but I would update these specific areas:

Old:

SET @PrimaryKeyID = SCOPE_IDENTITY()

Modern preferred approach:

OUTPUT INSERTED.Id

---

Old:

BEGIN TRANSACTION
...
COMMIT TRANSACTION

Modern production approach:

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- operations

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

---

Old:

FROM @Object_Table AS Objects,
     @Data_Table AS Data

Modern:

FROM ObjectTable AS O
CROSS JOIN DataTable AS D

or, where an actual relationship exists:

FROM ObjectTable AS O
INNER JOIN DataTable AS D
    ON O.Id = D.ObjectId

---

Old:

INSERT ...
VALUES (...);

INSERT ...
VALUES (...);

Modern set-based approach:

INSERT ...
VALUES
    (...),
    (...),
    (...);

---

One important architectural point

The biggest change from older SQL development is that the database is no longer necessarily responsible for orchestrating every multi-table operation.

In a modern ASP.NET Core + EF Core architecture, you may have:

React / Angular
       ↓
ASP.NET Core API
       ↓
Application / Service Layer
       ↓
EF Core / Dapper
       ↓
SQL Server

The transaction boundary can be managed at the service/application layer, while SQL Server remains responsible for constraints, relationships, indexes, transactions and data integrity.