• While a MERGE is not the only way to accomplish the task, we have no information from you to tell us what other things we'd need to take into consideration as to performance or any other characteristic. For example, WHY are you asking? Is it that this doesn't perform well, or that it "sure would be nice if it performed better" ? Typically, the only way to find out is to test an alternate methodology. Based solely on your description, you appear to need to just find all the identity values and their source records that don't exist in the new table, and insert them, plus insert the ID values in another separate table.

    These tasks could certainly be accomplished in separate query steps, and you could then encapsulate the entire query in a transaction to help solve potential problems from any breakage that might occur part-way through (e.g. running out of space in tempdb), if necessary. Can you elaborate?

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)