Solved

Select Count table1 insert top 30000 row into table2?

Posted on 2006-06-29
7
980 Views
Last Modified: 2010-08-05
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
Comment
Question by:kvnsdr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 25

Expert Comment

by:dstanley9
ID: 17013381
try

INSERT INTO table2
(SELECT TOP 30000 * FROM table1)

DELETE TOP 30000 FROM table1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17013385
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 17013740
Will you need to query against the archived records? If so look into horizontal partitions.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 18

Expert Comment

by:ShogunWade
ID: 17013877
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
 
LVL 1

Author Comment

by:kvnsdr
ID: 17014118
I'm using SQL2005.

Is the ORDER BY needed?
0
 
LVL 21

Expert Comment

by:Yurich
ID: 17014776
>>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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17015966
>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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question