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)