SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 copy some data of a gigantic table to another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agismark
Starting Member

8 Posts

Posted - 01/21/2013 :  17:10:50  Show Profile  Reply with Quote
Hello
I 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 size
The 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 too
3. its not indexed so a simple select need around 1 hour to complete
If 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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/21/2013 :  19:46:15  Show Profile  Reply with Quote
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 like

DECLARE @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 < @Date2

then set @date1 = @date2 and @date2 = dateadd(month,3,@date1)

Lather, rinse and repeat.

Jim


Everyday I learn something that somebody else already knew
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4344 Posts

Posted - 01/21/2013 :  20:45:40  Show Profile  Reply with Quote
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.
Go to Top of Page

agismark
Starting Member

8 Posts

Posted - 01/21/2013 :  21:10:16  Show Profile  Reply with Quote
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
Go to Top of Page

agismark
Starting Member

8 Posts

Posted - 01/22/2013 :  06:41:57  Show Profile  Reply with Quote
nevermind
I did it and it worked
maybe it took sometime but for now data seems to be ok
thanks for your help
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000