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...

  • Do you want to insert rows into the table Person? Or update existing ones? Or do you want to insert into Person and then UPDATE FinancialInstitution? Apr 9, 2014 at 16:19
  • Your query is only updating the Person table. You can capture the inserted.ID, but not the FinancialInstitution.ID unless you use it in the insert portion. The way your query sits, if you removed the OUTPUT clause, you would get an error because the number of columns in your INSERT statement does not match the SELECT statement.
    – datagod
    Apr 9, 2014 at 16:22
  • ypercube: I want to Insert into Person and then Update FinancialInstitution with the Id of the new row in Person. Apr 9, 2014 at 16:25
  • datagod: I know its only updating, this query is the nested level of the future solution. But I'm already stuck there. Right I can't add Id into the selection if I don't insert it. Apr 9, 2014 at 16:29
  • 1
    @YugoAmaryl, you can try to adopt this example Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts
    – DenisT
    Apr 9, 2014 at 16:32

1 Answer 1


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

( PersonId INT, 
  FinancialInstitutionId INT
) ;

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

USING FinancialInstitution AS fi
  ON 1 = 0
  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:

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

Test at: SQL-Fiddle


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.