I'm re-writing a C# console app from single insert to do bulk inserts
based on advice here, I need to bulk insert into a staging table (with id identity seed)
Then I load into temp tables, do a little processing, and then insert into the main table
This is for a real time 24/7 so it needs to be as fast and efficient as possible
1) what is the fastest way to bulk insert data from c# console app, to staging table (every 1 min, approx 50,000-150,000 records) – I'm planning on using SqlBulkCopy
2) Does having id with identity seed on the staging table slow down things ? should i be using a heap?
3) My main question and the highest risk area is inserting to the main busy table so this needs to be as fast as possible to reduce locking: when doing the bulk insert into main table, should i be pre-ordering the data so it matches the main clustered index (its on 3 columns: sensorId, DateTime, pkId) or will the I just be sorting twice? this table is extremely busy.
Any other advice on this area appreciated.
this whole bulk insert process is just a Stored Procedure, and i was thinking of calling it every 1 min from sql agent to make sure it runs (not sure if this is best solution)
sql server 2017 ent.