Sample Header Ad - 728x90

Insert with OUTPUT correlated to sub query table

24 votes
1 answer
17983 views
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...
Asked by Yugo Amaryl (433 rep)
Apr 9, 2014, 03:53 PM
Last activity: Apr 9, 2014, 05:28 PM