How to Insert Multiple Values into Multiple Tables in a Single Statement? – Interview Question of the Week #290

Question: How to Insert Multiple Values into Multiple Tables in a Single Statement?

Answer: One of the most popular questions, I often receive from my client  Comprehensive Database Performance Health Check. I often see people implementing trigger for this scenario. I personally do not like triggers at all. My goal in my career is to build trigger-less systems. Anything you can do with triggers you can also do it without triggers just by properly architecting your system and also wrapping various logic in a single logic.

Today we will learn how we can insert Multiple Values into Multiple Tables in a Single Statement. To make it possible you will have to use the T-SQL feature OUTPUT which was introduced in the year 2005. I have previously blogged about them over here: OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE and SQL SERVER – Output Clause in Simple Examples.

Here is the script where you can see how you can insert data into Table1 and Table2 all together in a single statement.

-- Creating two tables
CREATE TABLE Table1 (ID1 INT, Col1 VARCHAR(100))
GO
CREATE TABLE Table2 (ID2 INT, Col2 VARCHAR(100))
GO
-- Inserting into two tables together
INSERT INTO Table1 (ID1, Col1)
OUTPUT inserted.ID1, inserted.Col1
INTO Table2
VALUES(1,'Col'), (2, 'Col2')
GO
--Selecting from both the tables
SELECT *
FROM Table1
GO
SELECT *
FROM Table2
GO
-- Clean up
DROP TABLE Table1
GO
DROP TABLE Table2
GO

When you run the above code, you will notice that there are two rows each in Table1 and Table2. I personally find this question interesting as it tests the knowledge of the OUTPUT clause.

I hope you find this blog post interesting. Let me know if you have any questions or know similar tricks which we can share with everyone. You can stay in touch on LinkedIn.

Reference: Pinal Dave (https://blog.sqlauthority.com)