0

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?

Mike S
  • 157
  • 3
  • 13
  • 1
    If you are starting with multi row TVP then I usually loop my TVP with cursors and then insert using SCOPE_IDENTITY after each insert into table Interests and then take the value of SCOPE_IDENTIY and use that to insert into Interest2Currencies. Please understand SCOPE_IDENTITY if you do not understand it already. – Sql Surfer Feb 23 '18 at 01:12
  • Thanks @SqlSurfer I was not aware of https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – Mike S Feb 23 '18 at 18:19

1 Answers1

1

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.

David T. Macknet
  • 3,112
  • 3
  • 27
  • 36
  • Thanks fot the information, I'm not actually inserting multiple parents so no need for additional detail (until I cross that bridge :) but your insight was definitely helpful with my concern. – Mike S Feb 23 '18 at 18:18