Transact-SQL: insert into two tables, use newly created values with output clause

Transact-SQL: insert into two tables, use newly created values with output clause

Microsoft SQL

Tables:

Table1 Columns: Id, Name

Table2 Columns: Id2, Surname, Table1Id

Data:

Name: Adam 

Surname: Sandler

Query:

INSERT INTO Table1(Name) 

OUTPUT inserted.Id, 'Sandler' INTO Table2 (Table1Id, Surname)  

VALUES('Adam');

Query will not work with foreign keys defined. In order to use it with foreign keys you need to switch off constrain check.

f.e.

ALTER TABLE Table2 NOCHECK CONSTRAINT ALL

GO

Query

ALTER TABLE Table2 CHECK CONSTRAINT ALL

GO