How to Efficiently Insert Data into Multiple SQL Server Tables Simultaneously?

Simultaneous Insertion into Multiple SQL Server Tables

Inserting data into multiple tables simultaneously can be a challenge, especially when the tables have foreign key relationships. A common approach is using the INSERT-SELECT-INSERT method, which involves inserting data into the first table, retrieving the primary key, and then inserting that key into the second table.

However, this method can become inefficient for large-scale insertions. For such scenarios, SQL Server provides an alternative solution:

insert into [table1] ([data])
output inserted.id, inserted.data into table2
select [data] from [external_table]

This query inserts data into both tables in a single transaction. The OUTPUT clause retrieves the inserted primary key and data from the first table and inserts them into the second table.

Example:

CREATE TABLE [table1] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [data1] [varchar](255) NOT NULL,
  CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED ([id] ASC)
);

CREATE TABLE [table2] (
  [id] [int] IDENTITY(1,1) NOT NULL,
  [table1_id] [int] NOT NULL,
  [data2] [varchar](255) NOT NULL,
  CONSTRAINT [PK_table2] PRIMARY KEY CLUSTERED ([id] ASC)
);

INSERT INTO [table1] ([data1])
OUTPUT INSERTED.ID, INSERTED.DATA1 INTO [table2] ([table1_id], [data2])
SELECT [data1] FROM [external_table];

This query will insert data from the external table into both table1 and table2 in a single operation. The data in table1 will be used to create the foreign key relationship in table2.

Handling Different Data Columns:

In some scenarios, the data columns in the source and destination tables may differ. In this case, the MERGE statement can be used:

MERGE INTO [table1] AS t
USING [external_table] AS s
ON 1=0 -- modify this predicate as necessary
WHEN NOT MATCHED THEN INSERT (data)
VALUES (s.[col1])
OUTPUT INSERTED.ID, s.[col2] INTO [table2];

This query performs an upsert operation, inserting rows into table1 that do not already exist, and updating existing rows. The OUTPUT clause retrieves the inserted primary key and the corresponding data from the external table and inserts them into table2.

The above is the detailed content of How to Efficiently Insert Data into Multiple SQL Server Tables Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!