We help IT Professionals succeed at work.

bulk insert and return output data (not just ID)

57 Views
Last Modified: 2020-04-25
I'm changing from a per record insert to bulk insert
I need to bulk insert every 1 min
There might be approx 50,000 records inserting


However I have to re-architect somethings because I was using the InsertedRecordId for multiple reasons further down the code


Each of the records inserting contains a bunch of stuff including:
ImeiNumber
GpsDateTime

All these records are inserted into the LOGGING table

I also have a DEVICES table with the LatestRecordId and LastestRecordGpsDateTime
After the bulk insert, I also need to update each record in the DEVICES table with its LatestRecordId and LastestRecordGpsDateTime

When i do a bulk insert, there could be around 15 records for same device

I've read that you can return outputs of these 50,000 rows from a stored procedure after bulk inserting and figure out in c# which ones to update
Alternatively I could bulk insert all but the latest record, and do these 1 at a time and return the ID to update the DEVICES table.

Can anyone comment on a good solution for the above

C#, .net core 3, sql server 2017 ent
Performance is a very large concern with the solution
Comment
Watch Question

Database Reliability Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
websssCEO

Author

Commented:
Interesting, I had not thought of a staging table.

The database is very busy, I assume going to a staging table first means it's will insert into the main table quicker because the data is already inside the DB?
Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
There are a few advantages.
- You can index (or not) the stage table and target table individually. It might turn out to be faster to have the stage as a heap, and truncate after the load is completed.
- It allows you move your processing requirement to an asynchronous batch process. You can also scale this, potentially having multiple jobs running - this can be a bit trickier to architect and you'll need some way of maintaining consistency.
- Inserts into the main table can be controlled. Maybe lots of smaller inserts can allow better concurrency.
Matt BowlerDatabase Reliability Engineer
CERTIFIED EXPERT

Commented:
websssCEO

Author

Commented:
Thanks Matt

Our goal is to move away from Sql server and move to postgres with timescale db which is purpose built for our use case, and can handle large volumes of inserts
We have it working on a one of the databases and it does seem a good solution

Having said that InMemory OLTP was an interesting read!

Based on what you have said regarding the #temptable, I'm assuming I could do the following
For clarity, there are 5 servers all inserting into the DB, on their own schedules.
Another issue is I need the inserted ID on some of these records so I can insert/update other tables

So here is what I was thinking based on your feedback:

1. Batch all records into #NewestLiveData temp table

2. write a sql agent job which does the following

 -- a. select N ...(call it 1000 records),  from this #NewestLiveData temp table and put the tempTableStartID / tempTableEndID into local variables so we know what to purge after its complete

 -- b. insert these records into MAIN table and return their inserted Id's, and a few output variables so i can do the below:

 -- c.  sub query these records for certain "types", one example is "entered location", and save these rows to tblLocationVisit table (each insert has to contain the MAINTABLE.ID from point b)

 -- d.  sub query these N records inserted, and get the "latest record" for each distinct vehicle, then update DEVICES table with the timestamp and ID from point b.

The issue I see here is that I need the inserted ID on some of these records so I can then update/insert into other tables
Is this a dead end approach based on this ? can i return these outputs and match them to the data i just inserted?
websssCEO

Author

Commented:
Thanks Matt
Its now running in production as you suggested and works great!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.