Author |
Topic |
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-27 : 21:04:30
|
I want to insert 1.5 billions records every day from one database table to another database parition table on same server.What is the efficient query to perform the above operation with better performance.BEGININSERT destinationDB.dbo.StudentList (StFName,StLName,StEmail,OrderID)select StFName,StLName,StEmail,OrderID from SourceDB.dbo.studentlistendThanks for your help in advance ! |
|
X002548
Not Just a Number
15586 Posts |
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-27 : 22:44:28
|
Thanks I have the requirement to do that. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-28 : 05:17:24
|
quote: Originally posted by sqlfresher2k7 Thanks I have the requirement to do that.
few things to note1.make sure you schedule this data population out of normal office hours2. hope your destination table doesn't have too much indexes, if yes it might be worth dropping them before population and recreate it afterwards3. remember to update statistics after data dumping process ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 08:25:14
|
Thanks visakhIt has one indexes on a table I will drop the index before loading.Do I have to use bcp or the above insert script. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-28 : 08:40:58
|
bcp will be bulk operation so will be faster than insert------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
sqlfresher2k7
Aged Yak Warrior
623 Posts |
Posted - 2011-10-28 : 21:44:34
|
Thanks visakhIt has one indexes on a table I will drop the index before loading.Do I have to use bcp or the above insert script. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-29 : 01:00:50
|
quote: Originally posted by sqlfresher2k7 Thanks visakhIt has one indexes on a table I will drop the index before loading.Do I have to use bcp or the above insert script.
i thought i answered you in last postjust to reiteratebcp will be bulk operation so will be faster than insert------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
|
|