Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2005-07-21 : 08:37:57
|
| Guys, I have scenario where I have to do bulk inserts from one table to another. Since it involves inserts of over 10 millions rows I was wondering if there any option in Sql Server similar to Direct Load in Oracle (In direct load the data is not read into the memory instead directly from the disk hence efficient for Bulk Inserts in Oracle).I was also told that using Sql Server's DTS is also viable option, but does it mean DTS by passesthe memory. Any TSQL statements that would do Bulk Inserts that I am missing ???Any comments/suggestions would be helpful indeed.Thanks |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-07-21 : 11:56:47
|
| BULK INSERT WITH TABLOCK is much, much faster than DTS. The DTS utility is designed more for data transformation. If you need to manipulate the data as your importing it then DTS is the way to go. Otherwise BULK INSERT and BCP are best (In my opinion). But keep in mind that the "with tablock" option will put an exclusive lock on the table.To look over BULK INSERT open Books On-line and look at "BULK INSERT, BULK INSERT (described)"You may also want to set your DB recovery model to "simple". You can read more about the recovery models in Books On-line too.DanielSQL Server DBAwww.dallasteam.com |
 |
|
|
scelamko
Constraint Violating Yak Guru
309 Posts |
Posted - 2005-07-21 : 15:17:21
|
| But all the info given in books online is for Bulk Insert from a file to a table.I want to be able to do it from a table to table which books online doesnt provide the syntax to doAny ideas if it cna be done at allThanks |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-07-21 : 15:37:45
|
quote:
Originally posted by scelamko
But all the info given in books online is for Bulk Insert from a file to a table.I want to be able to do it from a table to table which books online doesnt provide the syntax to doAny ideas if it cna be done at allThanks
If it's already in a table then I would just use a standard SQL INSERT statement to move the data. If the data is being used and the move needs to not affect users then I would move the data in chunks of 500 or 1000 records and do this in off hours if possible. If its not a big deal to affect the users then I would create the new table with out indexes or drop the indexes if the table already exists. Then do the insert all at once and specify the "With TABLOCK" hint.DanielSQL Server DBAwww.dallasteam.com |
|
|
|
|
|
|
Bulk Inserts in Sql Server