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, 'a@x.com', 'x'), (2, 'b@x.com', '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, 'a@x.com'), (2, 'b@x.com');

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, 'a@x.com'), (2, 'b@x.com');

COMMIT TRANSACTION  

Answer 2

Method 1 - "Double Insert" using OUTPUT

Pros: single statement, no hidden triggers (method 2).

Cons: only works in this statement i.e. doesn't capture all insert events

INSERT INTO dbo.users (id)
  OUTPUT inserted.id
  INTO user_log (id)
VALUES (9)
     , (3)
     , (7)
;

Method 2 - Trigger

Pros: Captures all insert events

Cons: Triggers are "hidden" mechanisms

CREATE TRIGGER user_log_after_insert
  ON dbo.users
    AFTER INSERT
  AS
BEGIN
  INSERT INTO dbo.user_log (id)
  SELECT id
  FROM   inserted
  ;
END
;

Method 3 - Temp Table

Included for completeness for when using older versions of SQL Server that don't support method 1

CREATE TABLE #temp (
   id int
);

INSERT INTO #temp (id) VALUES (9);
INSERT INTO #temp (id) VALUES (3);
INSERT INTO #temp (id) VALUES (7);

BEGIN TRAN
  INSERT INTO dbo.users (id)
  SELECT id
  FROM   #temp
  ;
  INSERT INTO dbo.user_log (id)
  SELECT id
  FROM   #temp
  ;
COMMIT TRAN