SQL Server – Insert with OUTPUT Correlated to Subquery Table

insertoutput-clausesql server

I am modifying the structure of a database. The content of several columns of the table FinancialInstitution has to be transferred into the table Person. FinancialInstitution is linked to Person with a foreign key. Each FinancialInstitution needs the Id of its corresponding Person. So, for each new line inserted in Person, the id of this new line (IDENTITY) has to be copied back into the corresponding line of FinancialInstitution.

The obvious way of doing this is an iterative T-SQL code. But I'm interested in knowing if it's possible to do it only with set-based operations.

I imagined the inner-level of such a request would be something like:

INSERT INTO Person (Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
OUTPUT inserted.Id, FinancialInstitution.Id
SELECT Id, Street, Number, City, [State], PostCode, CountryId, PhoneNumber, Fax, Email
FROM FinancialInstitution;

Unfortunately, it seems OUTPUT can't correlate that way…

Best Answer

I guess you could (ab)use MERGE for this. First create a (temporary) table:

CREATE TABLE tempIDs
( PersonId INT, 
  FinancialInstitutionId INT
) ;

Then MERGE into Person (instead of INSERT), so you can use columns of the tables involved in the OUTPUT clause:

MERGE INTO Person 
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (Street1, Number1, City1, ...)
  VALUES (fi.Street, fi.Number, fi.City, ...)
OUTPUT inserted.Id, fi.Id
  INTO tempIDs ;

Then use the temp table to UPDATE FinancialInstitution:

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

Test at: SQL-Fiddle

Related Question