Importing JSON into multiple SQL tables using SQLBulkCopy or parameterized insert statements.

I have a several JSON source files (about 500 MB total size, with 76,000 individual records) that I'm working on importing into a SQL database using C#.  I prefer importing into SQL using SQLBulkCopy, but to use that for multiple tables I would need to effectively create the entire database in C# before importing, or else read through every JSON file once for each table I'm importing.  

Neither option which seemed good to me, so I have written code to import each line of data individually using parameterized SQL queries, but that hits the database once for each record on each table.  There is also a potential problem in that our database has a record keys table for the primary key on each database table, and in order to not be writing to that table after every row insert (doubling the number of database writes), I'm keeping track of it in C# and updating it at the end.  The code to keep track of the value is more complicated than I would like, which means the risk of an issue is higher than updating it once after a SQLBulkCopy.

The most important question is which would be faster, parsing the JSON files once for every database table and using SQLBulkCopy for one insert per table, or parsing the JSON files once and writing each individual line?  Also, if the times are similar, it might be worth rewriting it to parse the files once for each table anyway, just to make the code simpler and reduce the likelihood of primary key errors.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongSoftware Team LeadCommented:
what version of MS SQL are you using?

if you're using ver 2016 and above, you can read JSON content using OpenJSON method

VistarTechnologiesAuthor Commented:
I am using SQL 2016, but due to the number and complexity of the data manipulations would prefer to use C#.
Ryan ChongSoftware Team LeadCommented:
To read JSON in C#, you need to implement serialisation / deserialisation with your own classes.

you can start looking for this free library: Newtonsoft.Json
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.