Interview Question - How to INSERT data into two tables with one INSERT statement

Well the answer is simple use OUTPUT clause with your INSERT statement and you should be able to achieve it.

Given below is the example 


-- First Table in which we will INSERT data

DECLARE @TAB TABLE
(
NUM INT IDENTITY(50,1),
ID INT

)

-- Second table to hold the indentity value from the first table
-- This table will be populated using OUTPUT clause


DECLARE @TAB1 TABLE
(
ID1 INT

)


-- Inserting into First table while populating values inserted into its identity column into second ---table

INSERT INTO @TAB (ID)
OUTPUT INSERTED.NUM INTO @TAB1(ID1)
SELECT 100 UNION ALL
SELECT 10

-- displaying data from the second table


SELECT * FROM @TAB1

For more details on the OUTPUT clause please refer

https://msdn.microsoft.com/en-us/library/ms177564.aspx