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
LVL 1
kvnsdrAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>I'm using SQL2005.


SET ROWCOUNT 2000
DELETE table1
OUTPUT
  DELETED.col1, DELETED.col2 ...
  INTO @table1
  (
    col1 int, col2 varchar(20) ...
  )

INSERT INTO table2
  SELECT * FROM @table2

SET ROWCOUNT 0

>Is the ORDER BY needed?
yes, with the version I posted originally, otherwise the select and the delete might take different rows!
with the new OUTPUT feature, this is no longer needed.


0
 
dstanley9Commented:
try

INSERT INTO table2
(SELECT TOP 30000 * FROM table1)

DELETE TOP 30000 FROM table1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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,

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Atlanta_MikeCommented:
Will you need to query against the archived records? If so look into horizontal partitions.
0
 
ShogunWadeCommented:
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.
0
 
kvnsdrAuthor Commented:
I'm using SQL2005.

Is the ORDER BY needed?
0
 
YurichCommented:
>>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).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.