asked on

Select Count table1 insert top 30000 row into table2?

I'm attempting to archive my files from table1 to table2 by count.

I've been deleting them with the following sql, but would like to move them to an overflow table.

IF (SELECT COUNT(*) FROM table1) > 50000
BEGIN SET ROWCOUNT 20000 DELETE FROM table1 SET ROWCOUNT 0 END

Here's my start on the move sql:

IF (SELECT COUNT(*) FROM table1) > 50000
BEGIN SET ROWCOUNT 20000 insert top 30000 into table2 SET ROWCOUNT 0 END

dstanley9

try

INSERT INTO table2
(SELECT TOP 30000 * FROM table1)

DELETE TOP 30000 FROM table1

Guy Hengel [angelIII / a3]

if you use TOP, you should also use ORDER BY, to ensure that later you delete the same 20000 rows than those that you deleted.

note: if you are using SQL Server 2005, there will be an easier solution,

Atlanta_Mike

Will you need to query against the archived records? If so look into horizontal partitions.

ShogunWade

Question: " move them to an overflow table" ..... Why start archiving data at 50,000 rows ? This is not a vast number and well withing sql's capabilities.

kvnsdr

ASKER

I'm using SQL2005.

Is the ORDER BY needed?

Yurich

>>Is the ORDER BY needed?<<

yes, ms sql server can order (sort/fetch) the records in different order every time if you don't specify it (but not necessarely).

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial