Insert OUTPUT Insert.id to another table in multiple values insert

Question

Just for simplicity suppose I have two tables

user table (id, email)

user log table (id, date)

whatever id gets inserted in user table, same id should be inserted in user_log table also else transaction should fail.

How can I do this

BEGIN TRANSACTION

INSERT into user(id, email) OUTPUT Inserted.id (1, '[email protected]', 'x'), (2, '[email protected]', 'y')  

// I also want to be able to do
INSERT into user_log(id, date) values(1, date), (2, date) 

COMMIT TRANSACTION

Answer 1

You can insert the output directly into the user_log table:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
VALUES (1, '[email protected]'), (2, '[email protected]');

COMMIT TRANSACTION  

Example on SQL Fiddle

---

If you need to return the ids you can just add a second OUTPUT clause:

BEGIN TRANSACTION

INSERT INTO [User] (ID, Email)
OUTPUT inserted.id, CURRENT_TIMESTAMP INTO user_log(id, date)
OUTPUT inserted.id                      
VALUES (1, '[email protected]'), (2, '[email protected]');

COMMIT TRANSACTION