3

There are existing options for doing bulk insert into a single table with EF entities. Specifically, this SO question and using this class from David Browne.

In the case of trying to bulk insert rows into both a parent and child table, however, nothing jumps out as an option at that same level of convenience.

The 'hacks' I can think of (but I'm hoping there's at least one better option out there) include:

  1. generate the PK's and set the FK's before insert (in this scenario, we know nothing else is inserting at the same time), then do the bulk inserts of both (turning off IDENTITY_INSERT during the parent insert if necessary)
  2. bulk insert (using the linked SO question's approach) the parent rows, select them (enough columns to identify which parent row is which), generate child rows, bulk insert those
  3. generate the sql necessary to insert all the rows in a single batch, doing each parent and then all related children, using @@identity to fill in the FK for the child inserts

The 'pregenerate PK values' approach (I haven't actually tried it) seems fine, but is fragile (requires no other inserts to at least parent during the operation) and depends on either an empty table or selecting max(pk)+1 beforehand.

Since SqlBulkCopy seems to be built around inserting a table at a time (like bcp), anything that still lets sql generate the PK/identity column would seem to be built around 'dropping down' to ado.net and building the sql.

Is there an option outside of 'generate the tons of sql' that I'm missing? If not, is there something out there that already generates the sql for mass-insert into related tables?

Thanks!!

Community
  • 1
  • 1
James Manning
  • 13,429
  • 2
  • 40
  • 64
  • Your question seems to be based around not wanting to "generate the tons of sql". You could insert to a temp table then manage the normalise from there. But any way you do this you would still have to define where the data goes and write the code. – Russell Hart Dec 18 '11 at 20:08
  • 1
    Note you'd use SCOPE_IDENTITY not @@identity in any solution – gbn Dec 18 '11 at 20:19
  • @Russell - it's more about trying not to reinvent the wheel if someone's already solved this - bulk insert with linq-to-entities and linq-to-sql has come up in various threads over the years, but nothing I could find seems to address the 'related table' bit. If someone else hasn't already done it, I'm fine writing the sql calls, but at least at first glance that means doing the quoting/escaping of values manually since passing them in as sqlparameters doesn't seem viable (AFAICT) when the param count would be in the thousands or higher. – James Manning Dec 19 '11 at 04:32
  • @gbn - right, thanks - it's been awhile since using either, but you're right, scope_identity() would be better than @@identity. If there are multiple child inserts, I was thinking I might have to ident_current('parent') (or assign scope_identity to a variable and use that), but haven't tried it (this is assuming the child as its own identity PK) – James Manning Dec 19 '11 at 04:35

1 Answers1

0

The first rule of any foreign key constraint is that it must exist, as a primary key or unique constraint, in another table before inserted into the foreign key table.

This works great when you are adding a few rows at a time (traditional transaction processing environment). Howevere, you are trying to bulk insert into both at the same time. I'd term this as batch processing. Basically, the bulk update lock on the parent table is going to block the child table from reading it to check that the fk linkage is valid.

I'd say your 2 options would be 1.) leave the fk out entirely or 2.) Set the fk as nocheck before the bulk insert, then turn the check on after the bulk insert is complete with an alter table.

brian
  • 3,635
  • 15
  • 17
  • WRT the ordering, I'm happy to bulk insert the parent and then bulk insert the child - the 'gotcha' is around getting those PK's of the parent back for use in the child bulk inserts. The approach of setting the PK manually works around that (so the PK's of the parent rows and FK's of the child rows are all set before any inserts), but it's pretty hacky. If there were a bulk-insert option that left the parent EF entities with the PK values being set, that'd be sufficient as well, but I don't know of anything that does so. – James Manning Dec 19 '11 at 04:40