Similar to these other questions noted below, I have two tables with the structure:
create table parent (
recno int identity(1,1) primary key not null,
groupCode int,
parentdata varchar(80)
);
create table child (
parentrecno int not null,
childdata varchar(80)
)
I need to insert a few hundred thousand records quickly into these tables -- and the tables hold millions of other records unrelated to this insert and are never quiet. Because of the parent/child nature, it's not a good candidate (it seems) for SqlBulkCopy
.
In C# using SqlCommand
with INSERT
I'm getting about 400-500 records/second inserted, and this is a bit too slow. Pseudocode:
foreach(Record r in parentRecords)
{
Insert Fields from r into SqlCommand Parameters but not "recno"
Call ExecuteScalar to insert and fetch the inserted identity value (recno)
foreach(ChildRecord cr in parentRecords.Children)
{
Insert Fields from cr into SqlCommand Parameters
Insert the identity value (recno) from above into Parameters
(as parentrecno)
Call ExecuteNonQuery to insert the record
}
}
After reading those other posts, a though occurred to me. The groupCode
attached to the parent records is unique to that set of parent records I'm inserting. Would it work to:
- Bulk insert the parent records with
SqlBulkCopy
, letting the insert auto-generate the recno
identity field as usual.
Perform a SELECT
on just the inserted records:
select recno from parent where groupCode = @thisgroup order by recno;
Use the retrieved values to fill in the parentrecno
fields for the child records in memory
- Bulk insert the child records with
SqlBulkCopy
This would rely on the parent records going into the SQL table in the same order as they are in the original DataTable (and the identity values being assigned in that same order). Is this something I can rely on?
Related questions:
How to update Dataset Parent & Child tables with Autogenerated Identity Key?
SqlBulkCopy and DataTables with Parent/Child Relation on Identity Column
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…