• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1011
  • Last Modified:

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
0
kvnsdr
Asked:
kvnsdr
1 Solution
 
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
 
Atlanta_MikeCommented:
Will you need to query against the archived records? If so look into horizontal partitions.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
Guy Hengel [angelIII / a3]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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now