I have a task to programmatically generate 3 million different rows of data and insert them into a database table for test purposes.
I have written a VB.NET program that handles the generation of row data which can then be either INSERT-ed a row at a time straight into my database or written as a comma delimited row in a .csv file.
My issue is that some of the rows required a joining row added in another table, and the join is done an IDENTITY column value from my first table, and I won't know what that value is until after the row has been inserted.
So I have been using the VB.NET program to insert rows one by one, adding the joining row in the other table if required. This was working at a reasonable speed until the main table reached around 2 million rows, at which point it started taking much longer to INSERT a new row.
What is the best practice for achieving my goal? I have looked a little at Bulk Insert and OPENROWSET, but cannot figure out how to use them to populate two tables with different amounts of rows and a joining column which I don't know the value of.