This article is half-done without your Comment! *** Please share your thoughts via Comment ***
If you are a SQL Database Developer, the interviewer may ask this question.
How to insert the data into Multiple tables, in addition, please do using a single query only.
Look like a simple question, but it is not. There are no multiple options for this exercise because INSERT is a DML statement and it has to run in transaction block at a time.
Check few below options:
Create sample tables:
1 2 |
CREATE TABLE tbl_A (ID INT) CREATE TABLE tbl_B (ID INT) |
Approach 1:
1 2 3 4 |
INSERT INTO tbl_A OUTPUT INSERTED.ID INTO tbl_B SELECT 1 |
Approach 2:
Using stored procedure, we should control this kind of request.
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE usp_InsertAll (@ID INT) AS BEGIN INSERT INTO tbl_A VALUES (@ID) INSERT INTO tbl_B VALUES (@ID) END GO EXEC usp_InsertALL 2 GO |
Leave a Reply