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.

How to Insert Multiple Values into Multiple Tables in a Single Statement? - Interview Question of the Week #290 singleinsert-800x401

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.

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

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)

, ,
Previous Post
Do MAX Function Scan Table? – Interview Question of the Week #289
Next Post
What Happens If the Clustered Index is Disabled? – Interview Question of the Week #291

Related Posts

1 Comment. Leave new

  • Thanks for sharing this impressive blog. I really appreciate the work you have done, you explained everything in such an amazing and simple way. Such a wonderfully written post! I love how in-depth it was, and you quite literally covered all your bases. Hope to see a lot coming from you.

    Reply

Leave a Reply

Menu