Solved

Select Count table1 insert top 30000 row into table2?

Posted on 2006-06-29
7
952 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
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 142

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 142

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now