insert rows to other table

Question

Post reply

insert rows to other table

rpatil22

Ten Centuries

Points: 1371
More actions
February 8, 2009 at 2:53 pm

#126211

I want to insert 400k rows from main table to archive table.
I want to first insert 5000 rows and then delete from main table and then take next 5000 , do insert and delete and then do for next and next.....

Viewing 7 posts - 1 through 6 (of 6 total)

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

Bruce W Cassidy SSChampion Points: 12668 More actions · Answer 1

[font="Verdana"]Is there a question there? Or are you just wanting validation that it's a good approach to take?[/font]

Rob Fisk SSCommitted Points: 1861 More actions · Answer 2

Do you have a table structure sample? Is there a unique ID on the main table? Is this sql 2k or higher?

[font="Arial"]_______________________________________________________
Change is inevitable... Except from a vending machine.[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 3

here is a delete example. hope that helps, without more details it's tough.

SET ROWCOUNT 50000

WHILE 1=1

BEGIN

DELETE FROM dbo.myTable

WHERE MyField = My Condition

IF @@ROWCOUNT = 0

BREAK

END

SET ROWCOUNT 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rpatil22 Ten Centuries Points: 1371 More actions · Answer 4

There are total 450k rows in a table, it contains 2 yrs data.

I want to keep only 6 months data main table and rest I want to insert to archieve table and delete from main table.

So total rows coming out for inserting and deleting 400K.

The table having 20 columns and there is one "ArrivedDate" column (datetime) and I will compare that

against current getdate().

while inserting and deleting i want to do in batches of 5000 rows.

thanks.

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

How about doing this with DELETE & OUTPUT clause???

DECLARE@iRowCount INT,

@iBatchSize INT,

@sdtCurrentDate SMALLDATETIME,

@sdtDeleteAfterDate SMALLDATETIME

SELECT@iRowCount = 1,

@iBatchSize = 5000,

@sdtCurrentDate = CONVERT( VARCHAR(8), GETDATE(), 112 ),

@sdtDeleteAfterDate = DATEADD( YEAR, -2, @sdtCurrentDate )

WHILE ( @iRowCount > 0 )

BEGIN

DELETETOP( @iBatchSize )

FROMdbo.MainTable

OUTPUTDELETED.*

INTOdbo.ArchiveTable

WHEREArrivedDate >= @sdtDeleteAfterDate

AND ArrivedDate <= @sdtCurrentDate

SELECT@iRowCount = @@ROWCOUNT

END

--Ramesh

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 6

Hi

Why dont you do it in single short?? The amount of data that you are delaing is very small. why do you want to do it in small chunks?? If you do it in small chunks its more error prone.

Please let us know if you have any other difficulties.

Thanks -- Vijaya Kadiyala

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers