Moving data from table to table in same database

Question

Post reply

Moving data from table to table in same database

Mayr.L.Stephens

SSC Enthusiast

Points: 130
More actions
December 24, 2010 at 9:10 am

#232295

I am archiving some data. I will need to move 9 million records with each record having over 800 fields.
I tried SSIS Execute SQL task, a query that deletes from the main table and capturing OUTPUT for insert into,
because the records are so large I've been using begin /commit. There must be a 1,000 ways to do this. Looking for suggestions----MERRY CHRISTMAS
---delete with ouput
USE MyDB
GO
declare @TCount bigint
set @TCount = 1
WHILE @TCount >0
BEGIN ---not sure where to put COMMIT to keep the data package small
Delete Top (300) FROM dbo.MainTbl OUTPUT deleted.* into ArchiveTbl
WHERE ( Date_1 between '01/01/1901' and '12/31/2007'
or Date_2 between '01/01/1901' and '12/31/2007' )
set @Tcount= @@ROWCOUNT
IF @Tcount <1
BREAK
ELSE
CONTINUE
END

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281344 More actions · Answer 1

I think I would prefer to do the insert first and then delete from the source table.

Is the target table empty?

800 fields? Is this a warehouse table?

Have you considered doing this in a set-based or batch based method?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mayr.L.Stephens SSC Enthusiast Points: 130 More actions · Answer 2

I'm working with a 33-77 million record table. I need to take the data chunks off the these tables so I thought a Top 1000 delete with OUTPUT. As the table shrinks the query will cycle faster. My target table is empty. About the 800 fields it's not a datawarehouse just a monster table. I have a few tables I will need to do this too. I'm not to familiar with set based, batch based.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Will you be moving all data from the source table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mayr.L.Stephens SSC Enthusiast Points: 130 More actions · Answer 4

I will be moving data from a source table on the same server to a destination in the same database

SQLRNNR SSC Guru Points: 281344 More actions · Answer 5

A subset of that data or all of that data?

If you are moving all of it, have you considered a Select * into tablename

or maybe rename the old table?

How about the use of a staging table?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Mayr.L.Stephens SSC Enthusiast Points: 130 More actions · Answer 6

I'm moving a subset older records of the source table data. I can't rename it since I still need that for the subset of newer records. I've tried insert tbl1 from select * tabl2. It's too big I've tried dataflow(not very fast on same instance of server)

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 7

I don't know if it applies to your case, but if your able to set up partitioning on your table, switching out chunks of data will go wicked fast.

(of course you'd have to partition your table first, which could take some time)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Mayr.L.Stephens SSC Enthusiast Points: 130 More actions · Answer 8

I went off to read about partitioning as I understand it I would still need to write these records to the partion. So it would take as long to write to partition as write to archive table. Although the idea seemed perfect. Does that sound right?

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 9

Mayr.L.Stephens (12/28/2010)
I went off to read about partitioning as I understand it I would still need to write these records to the partion. So it would take as long to write to partition as write to archive table. Although the idea seemed perfect. Does that sound right?

If your source table is partitioned, you can just switch out the old records to your destination table. Which is really fast.

Some drawbacks:

* your table must of course be partitioned, which can bring some overhead in the beginning when you are setting this up

* there are some constraints when you switch out partitions (read them on BOL, there are quite a lot of them)

* if I'm not mistaken, you must have Enterprise Edition.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP