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.