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 |
agismark
Starting Member
8 Posts |
Posted - 2013-01-21 : 17:10:50
|
HelloI have this problem that i thought it would be easy but it turns to a headache. I have a very big table with 2.3 billion rows and 90 gb sizeThe table has a column of smalldatetime and some other columns...i want to copy everything from time x to time y to a new table. I have various problems with this database. 1. log file grow very big .2. tempdb file grows too3. its not indexed so a simple select need around 1 hour to completeIf it is possible I would like batch copy rows so if something go wrong I will continue from where it stopped.I guess i should do something like :insert into ais2.dbo.smaller_table (mmsi , date1 ,lng ,lat , [status] ,speed ,course ,heading) select * from [ais].[dbo].[gigantic table]where [ais].[dbo].[imis position report].date1 < @date1 and [ais].[dbo].[imis position report].date1 > @date2 ;please advice ,thanks a lot! |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-21 : 19:46:15
|
Doing it in chunks is probably the best way, especially without indexes. There's no connective tissue between you giganticTable and the position table. IF there are any dates in your giganticTable, they would make good bounds for the chunk. Try to find a chunk of about 100,000 rows. If you have a date in GiganticTable, then do something likeDECLARE @date1 date = '20110101'DECLARE @date2 date = dateadd(month,3,@date2)insert into ais2.dbo.smaller_table (mmsi , date1 ,lng ,lat , [status] ,speed ,course ,heading)select *from [ais].[dbo].[gigantic table]where aDate >= @date1 and aDate < @Date2then set @date1 = @date2 and @date2 = dateadd(month,3,@date1)Lather, rinse and repeat.JimEveryday I learn something that somebody else already knew |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-01-21 : 20:45:40
|
Since you don't have indexes, I'd suggest using SSIS. You can use that to control the insert/batch size without having to run multiple queries against a large source table. |
|
|
agismark
Starting Member
8 Posts |
Posted - 2013-01-21 : 21:10:16
|
thanks for your replies.is it possible to search each row of table if it is between two dates and if yes copy if not ignore and go to the next row?and repeat this action 1million times and then commit and repeat and commit?Im not sure if it is good idea to search entire table for dateA then search again for dateB etc . It takes really long |
|
|
agismark
Starting Member
8 Posts |
Posted - 2013-01-22 : 06:41:57
|
nevermind I did it and it workedmaybe it took sometime but for now data seems to be okthanks for your help |
|
|
|
|
|
|
|