?
Solved

Select Count table1 insert top 30000 row into table2?

Posted on 2006-06-29
7
Medium Priority
?
998 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 1000 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

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!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

771 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