CODEWITHSUNDEEP
sql-serverparent-childidentity
Mike S
Mike S

Reputation: 157

INSERT to parent-child with @@IDENTITY

I've read related other questions but could not find a direct answer so I hoped to confirm that the best (only?) way in T-SQL to INSERT into a parent and many to many child is via two separate statements as at http://sqlfiddle.com/#!18/00180/3

Note that in my actual case the params include a TVP for the child table and the INSERTS are within a stored procedure with a TRY -- CATCH block. Regarding the latter (and TSQL Try / Catch within Transaction or vice versa?) is a COMMIT/ROLLBACK useful or too much overhead for something simple like this?

Upvotes: 0

Views: 889

Answers (1)

David T. Macknet
David T. Macknet

Reputation: 3172

You wouldn't necessarily have to use a cursor, but you will have to manage the identity fields in some fashion. If you're trying to create both a parent and children records, then you'd need to do an insert into the parent, grab scope_identity, and feed that scope_identity into the children records.

If you're trying to insert multiple parent records and multiple children records, you've got a bit more complex scenario, which needs some additional description in your question. It's possible to do - you'd need to have identity_insert on if you wanted to do multi-row inserts into that parent table, and you'd be managing your identity columns explicitly (i.e., in your own statements, rather than relying on the identity property to create them for you). It can be done, and there are scenarios where it's more than appropriate (cursors are still miserable performers in mssql).

Give us more of a detailed question (with the code in the question, please - the code out there is pretty basic and really should be inlined here) and let's see what you're really asking.

Upvotes: 1

Related Questions