Software Development

Itzik Ben-Gan uses a simple generic data model to demonstrate how to copy data from one set of related tables to another, including dependent rows, while preserving the correct relationships between the rows. What makes the task complicated is the fact that all keys are surrogate keys generated by identity.One solution uses the MERGE statement with the OUTPUT clause, allowing you to return both the source and the target surrogate keys. The second solution relies on the sequence object instead of the identity column property—and it does satisfy the ordering requirement, thanks to the fact that T-SQL supports an OVER clause with the NEXT VALUE FOR function.

byItzik Ben-Gan