Handling Large Data Modifications

Kevin Feasel

2016-10-17

T-SQL

Jeff Mlakar shows how to insert, update, and delete large numbers of records with T-SQL:

Using T-SQL to insert, update, or delete large amounts of data from a table will results in some unexpected difficulties if you’ve never taken it to task.

Let’s say you have a table in which you want to delete millions of records. If the goal was to remove all then we could simply use TRUNCATE. However, if we want to remove records which meet some certain criteria then executing something like this will cause more trouble that it is worth.

I do like the delete process.  The update process is going to run tableRows/batchSize full scans, so I’m not as fond of that one.  Do read the whole thing.

Related Posts

Updating Tables With Faked Data

Phil Factor continues his data obfuscation series: We are taking a slow-but-steady approach. We rewrite our code from the previous blog post that assembles the string; it now uses a view to get its random numbers, and we’ll speed it up slightly by putting a bit more intelligence into the markov table. We then put […]

Read More

COUNT And NULL Values

Kenneth Fisher digs into the COUNT() function and sees how it deals with NULL values: Count the number of values 1 2 3 SELECT COUNT(FieldName) FROM TableName; -- or SELECT COUNT(ALL FieldName) FROM TableName; The ALL argument is the default and is unnecessary (I didn’t even know it existed until I started this post). Here you are counting […]

Read More

Categories

October 2016
MTWTFSS
« Sep Nov »
 12
3456789
10111213141516
17181920212223
24252627282930
31