January 5, 2018 at 2:10 pm
I got 100 million rows to export data from one table to another table. Do you think import/export wizard is safe option? If not how do you do BCP with date field column. I am worried that import/export wizard will make log file grow big. However, not sure with BCP..Does BCP also make log file grow? What would be the best option you would suggest?
January 5, 2018 at 2:58 pm
In case you haven't seen it.
https://serverfault.com/questions/69135/what-is-the-difference-between-bulk-logged-and-simple-recovery-model-which-one/69137
In my opinion BCP requires a trip outside the database and it is a waste of resources.
January 5, 2018 at 9:49 pm
RandomStream - Friday, January 5, 2018 2:58 PMIn case you haven't seen it.
https://serverfault.com/questions/69135/what-is-the-difference-between-bulk-logged-and-simple-recovery-model-which-one/69137In my opinion BCP requires a trip outside the database and it is a waste of resources.
On the BCP thing, "It Depends". If the target table is on a different server than the source, it's frequently one of the fastest methods especially if you use the "native" format and then use a "Minimally Logged Import" using BULK INSERT to import the data on the target.
For transmission of that amount of data between two tables on the same server, then "Minimal Logging" with the Clustered Index already in place is the way to go for me.
Although it's 9 years old now, the information contained in the following MS whitepaper is still pretty much spot on. The only thing that they left out is the fact that you frequently need to use OPTION(RECOMPILE) to get minimal logging if variables are involved.
https://technet.microsoft.com/en-us/library/dd425070(v=sql.100).aspx
p.s. There's a lot of bad info in the post you cited. For example, BULK INSERT doesn't just work with "CSV or fixed width files" and unless you really need to use TF610, the TABLOCK hint is essential to achieve Minimal Logging in the presence of a Clustered Index even for an empty table. The largest benefits of using TF610 is Minimal Logging of new pages and parallel, non-overlapping loads.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2018 at 2:46 am
This was removed by the editor as SPAM
January 8, 2018 at 8:16 am
subramaniam.chandrasekar - Monday, January 8, 2018 2:46 AMAdmingod - Friday, January 5, 2018 2:10 PMI got 100 million rows to export data from one table to another table. Do you think import/export wizard is safe option? If not how do you do BCP with date field column. I am worried that import/export wizard will make log file grow big. However, not sure with BCP..Does BCP also make log file grow? What would be the best option you would suggest?Please use SSIS,
Create an OLE DB source transformation and pull the records from the source.
Create an SQL Server destination and push onto destination using T SQL statements.
Please do not perform any transformations on the input data while loading. This will create / consume more temp db space in your server.
The performance is purely depending upon your server hardware configurations.Hope this helps.
I wouldn't use SSIS for this. It's an unnecessary complication especially since you're recommending the push be done with T-SQL statements anyway.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2018 at 8:31 am
Jeff Moden - Monday, January 8, 2018 8:16 AMsubramaniam.chandrasekar - Monday, January 8, 2018 2:46 AMAdmingod - Friday, January 5, 2018 2:10 PMI got 100 million rows to export data from one table to another table. Do you think import/export wizard is safe option? If not how do you do BCP with date field column. I am worried that import/export wizard will make log file grow big. However, not sure with BCP..Does BCP also make log file grow? What would be the best option you would suggest?Please use SSIS,
Create an OLE DB source transformation and pull the records from the source.
Create an SQL Server destination and push onto destination using T SQL statements.
Please do not perform any transformations on the input data while loading. This will create / consume more temp db space in your server.
The performance is purely depending upon your server hardware configurations.Hope this helps.
I wouldn't use SSIS for this. It's an unnecessary complication especially since you're recommending the push be done with T-SQL statements anyway.
SSIS or BCP will both let you control records per transaction pretty easily, just doing an INSERT INTO for 100 million records might not be ideal 😛
January 10, 2018 at 6:41 am
This was removed by the editor as SPAM
January 10, 2018 at 7:59 am
subramaniam.chandrasekar - Wednesday, January 10, 2018 6:41 AMZZartin - Monday, January 8, 2018 8:31 AMJeff Moden - Monday, January 8, 2018 8:16 AMsubramaniam.chandrasekar - Monday, January 8, 2018 2:46 AMAdmingod - Friday, January 5, 2018 2:10 PMI got 100 million rows to export data from one table to another table. Do you think import/export wizard is safe option? If not how do you do BCP with date field column. I am worried that import/export wizard will make log file grow big. However, not sure with BCP..Does BCP also make log file grow? What would be the best option you would suggest?Please use SSIS,
Create an OLE DB source transformation and pull the records from the source.
Create an SQL Server destination and push onto destination using T SQL statements.
Please do not perform any transformations on the input data while loading. This will create / consume more temp db space in your server.
The performance is purely depending upon your server hardware configurations.Hope this helps.
I wouldn't use SSIS for this. It's an unnecessary complication especially since you're recommending the push be done with T-SQL statements anyway.
SSIS or BCP will both let you control records per transaction pretty easily, just doing an INSERT INTO for 100 million records might not be ideal 😛
Thanks Jeff & Zzartin, When we look to load 100 million of rows / very huge volume of records from a source to destination, We should have a perfect hardware setup, without having adequate hardware, we might have to face many issues while loading. This was my opinion.
If you have a look at the link I posted, you may be able to overcome some of the hardware problems by using the parallel load method in that article combined with minimal logging with a little help from Trace Flag 610. At the very least, the minimal logging will make things work at about twice the speed of doing it in a "standard" fashion. I believe SSIS can also be accomplish this in a similar manner if that's your tool of choice.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 10, 2018 at 9:49 am
Jeff Moden - Wednesday, January 10, 2018 7:59 AMsubramaniam.chandrasekar - Wednesday, January 10, 2018 6:41 AMZZartin - Monday, January 8, 2018 8:31 AMJeff Moden - Monday, January 8, 2018 8:16 AMsubramaniam.chandrasekar - Monday, January 8, 2018 2:46 AMAdmingod - Friday, January 5, 2018 2:10 PMI got 100 million rows to export data from one table to another table. Do you think import/export wizard is safe option? If not how do you do BCP with date field column. I am worried that import/export wizard will make log file grow big. However, not sure with BCP..Does BCP also make log file grow? What would be the best option you would suggest?Please use SSIS,
Create an OLE DB source transformation and pull the records from the source.
Create an SQL Server destination and push onto destination using T SQL statements.
Please do not perform any transformations on the input data while loading. This will create / consume more temp db space in your server.
The performance is purely depending upon your server hardware configurations.Hope this helps.
I wouldn't use SSIS for this. It's an unnecessary complication especially since you're recommending the push be done with T-SQL statements anyway.
SSIS or BCP will both let you control records per transaction pretty easily, just doing an INSERT INTO for 100 million records might not be ideal 😛
Thanks Jeff & Zzartin, When we look to load 100 million of rows / very huge volume of records from a source to destination, We should have a perfect hardware setup, without having adequate hardware, we might have to face many issues while loading. This was my opinion.
If you have a look at the link I posted, you may be able to overcome some of the hardware problems by using the parallel load method in that article combined with minimal logging with a little help from Trace Flag 610. At the very least, the minimal logging will make things work at about twice the speed of doing it in a "standard" fashion. I believe SSIS can also be accomplish this in a similar manner if that's your tool of choice.
I was going to ask more about trace flag 610 but found this instead. Thought I'd share.
SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply