INSERT INTO table1
(field1, field2, field3)
VALUES
(value1, value2, value3)
INSERT INTO table2
(field1, field2)
VALUES
(ID созданный в предыдущем запросу, value2)

FOREIGN KEY (user_id)  REFERENCES user (id) ON DELETE CASCADE

INSERT INTO user (
		username, 
		email
)
OUTPUT INSERTED.id, 'text'
INTO table2(
user_id,
message
)
VALUES (
		'user',
		'user@example.org',
)

DECLARE @MyTableVar table( NewScrapReasonID smallint,  
                           Name varchar(50),  
                           ModifiedDate datetime);  
INSERT Production.ScrapReason  
    OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate  
        INTO @MyTableVar  
VALUES (N'Operator error', GETDATE());  

--Display the result set of the table variable.  
SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar;  
--Display the result set of the table.  
SELECT ScrapReasonID, Name, ModifiedDate   
FROM Production.ScrapReason;