February 19, 2015 at 9:55 pm
Hello All,
I want to update data in destination table from Source table. Destination table already has data and source table has millions of rows.
What are best practices I must follow to load such data.
February 19, 2015 at 10:24 pm
In order to provide any meaningful answer, some more information must be provided, i.e. are the two tables in the same database, different databases on the same server, different SQL Servers, destination on SQL Server and the source on another type of database server, source on a mainframe, spreadsheet,???
Try to describe the environment as completely as possible, include the DDL of both source and destination, server software versions etc.
😎
February 19, 2015 at 11:35 pm
In addition to the Eirikur response, Please also share what working you have already done against it so far for the problem your are facing.
February 20, 2015 at 2:44 am
Bhushan Kulkarni (2/19/2015)
Hello All,I want to update data in destination table from Source table. Destination table already has data and source table has millions of rows.
What are best practices I must follow to load such data.
Do not use a row-by-row method, such as a cursor or the OLE DB command in SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2015 at 3:35 am
Here are a few suggestions in addition to what Koen already said.
You might want to drop all indexes and foreign keys from the table you're loading to, depending on how you're doing the load. Just remember to recreate them when finished and to use the WITH CHECK option on foreign keys.
You should also be concerned about the size of the transaction log on the database you're loading into. Either grow it to sufficient size ahead of your load assuming you have adequate disk space, or, figure out a way to break your load into smaller chunks in order to make the transaction size smaller.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2015 at 4:16 am
Grant Fritchey (2/20/2015)
Here are a few suggestions in addition to what Koen already said.You might want to drop all indexes and foreign keys from the table you're loading to, depending on how you're doing the load. Just remember to recreate them when finished and to use the WITH CHECK option on foreign keys.
You should also be concerned about the size of the transaction log on the database you're loading into. Either grow it to sufficient size ahead of your load assuming you have adequate disk space, or, figure out a way to break your load into smaller chunks in order to make the transaction size smaller.
And if your backup policy allows it, change the recovery model to simple.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 20, 2015 at 5:46 am
Is this a one time event or is it scheduled?
Make sure you have backups.
February 20, 2015 at 6:11 am
Koen Verbeeck (2/20/2015)
Grant Fritchey (2/20/2015)
Here are a few suggestions in addition to what Koen already said.You might want to drop all indexes and foreign keys from the table you're loading to, depending on how you're doing the load. Just remember to recreate them when finished and to use the WITH CHECK option on foreign keys.
You should also be concerned about the size of the transaction log on the database you're loading into. Either grow it to sufficient size ahead of your load assuming you have adequate disk space, or, figure out a way to break your load into smaller chunks in order to make the transaction size smaller.
And if your backup policy allows it, change the recovery model to simple.
Good point. But, after changing it back to Full, make sure you take a full backup.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2015 at 7:45 am
How wide is the table, how sparsely is the data populated, how many indexs are on the target table? Millions of rows doesn't necessarily mean a huge data size, just a large number of rows.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply