In trying to use this article to tailor a solution for my organization, I have encountered a bit of a snag.

Our "user" information is kept in two tables which we call UserAuthentication and Users. The UserAuthentication table contains the IDENTITY column and it gets created first, its IDENTITY value is captured after the INSERT with a SCOPE_IDENTITY() and then this value is used when creating a new Users record using an INSERT statement.

Assuming that I have all of the records to be merged in a staging table which contains fields destined for both the UserAuthentication and Users tables, how might I most efficiently utilize the MERGE statements to create records in both tables? Should I structure a stored procedure to handle this as MERGE against the UserAuthentication table first, then follow it with a MERGE against the Users table?

Would another solution be to perform the MERGE against the UserAuthentication table, then capture the resulting INSERT records into a temp table that I can use as a basis for INSERTS on the Users table?