Avatar of LovinSpoonful
LovinSpoonful
 asked on

How to bulk update / insert / add rows to a datatable without writing to the database

I have a large SQL Server database and run a complex transformation sequence against the data.  This requires a very long series of sql bulk updates, inserts, adds, etc.  This process takes 3-4 minutes to run.  Most of that time is wasted on disk i/o as each sql statement updates real tables in SQL Server.  I am trying to find a way to do 'sql-like' bulk updates in memory (for instance using a cached SqlDataAdapter dataset).  

I looked at LINQ but it doesn't support bulk (set-based) updates.  I need this process to run in 30 seconds, not 3 minutes.  I don't need to update the tables in the sql database until the process is complete.

Is there a way to do all the transformation within data containers that are in RAM, then finally export the final results to SQL server ?  I am talking about 100's of transforms, not a few.  And NO I cannot use a for/each loop to update each row individually - that would take much longer to run.

Could you please submit actual code snippetts, not general advice, if you have a method to do this.  Thanks in advance for your assistance.
Visual Basic.NET

Avatar of undefined
Last Comment
LovinSpoonful

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Jacques Bourgeois (James Burger)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
LovinSpoonful

ASKER
Thanks for your comment.  What you are describing above is writeback to the SQL Server database, correct?  

What I am looking for is a method to bulk update something in memory.  For instance, I have a dataset with 5 tables and I want to denormalize the data, passing info from one table to another.  I would like to do this within the dataset without writing to the sql server database, so that it would be faster.

Or, I would like to know how to run a bulk update query with a join on an Array, Dictionary, List, or other data object so that one 'table' (array) of information stored in memory could be joined to another 'table' and data passed between them.

I do not want to update the sql database because this involves writing data to disk.  I am looking for something faster than writing to the sql database.  I already have a method for doing bulk updates very quickly to the database, but this is not 'quick enough' when you have a few hundred of them and you need to get it all done in seconds instead of minutes.


Thanks again for your comment and let me know if I am missing the gist of your suggestion.


SOLUTION
Jacques Bourgeois (James Burger)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
LovinSpoonful

ASKER
Looks like Data Entities also requires you to make bulk update statements on the sql server database; encourages you to use Stored Procedures to do so.

Well, perhaps what I'm looking for does not exist.  I just thought you could query arrays like you can query tables, but perhaps not.

Thanks
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
LovinSpoonful

ASKER
Thanks guys for your assistance.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck