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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 copy some data of a gigantic table to another

Author  Topic 

agismark
Starting Member

8 Posts

Posted - 2013-01-21 : 17:10:50
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
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 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
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.
Go to Top of Page

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
Go to Top of Page

agismark
Starting Member

8 Posts

Posted - 2013-01-22 : 06:41:57
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
   

- Advertisement -