Registers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Registers

Author

Topic

cleverson
Starting Member

2 Posts

Posted - 2003-11-05 : 13:47:50

Hello.
(sorry my english)
I made a DTS to copy data from a database to excel file.
Trouble: The quantity of register is most than 65,XXX , that is, the spreadsheet in the excel doesn´t support.
I´m thinking to do two queries and save data in two spreadsheets. First, "select top 65000 from table".
The second query, I have to take remainder of data from the table.
p.s. "select top XXX from table order by DESC" don´t work.

How do I make this (to take remainder)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-05 : 13:55:13

Why would you want that much data in a spreadsheet?

Anyway, what do you mean ORDER BY DESC doesn't work? It should work. Check out this example:



SET NOCOUNT ON

CREATE TABLE Table1
(
Column1 INT NOT NULL
)

INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(1)
INSERT INTO Table1 VALUES(2)
INSERT INTO Table1 VALUES(2)
INSERT INTO Table1 VALUES(2)
INSERT INTO Table1 VALUES(2)
INSERT INTO Table1 VALUES(2)

SELECT TOP 5 *
FROM Table1
ORDER BY Column1 ASC

SELECT TOP 5 *
FROM Table1
ORDER BY Column1 DESC

DROP TABLE Table1

You should get the top 5 "1" rows, then the top 5 "2" rows.

Tara

cleverson
Starting Member

2 Posts

Posted - 2003-11-05 : 14:18:57

I have 125,000 rows.

Don´t work because:

In some cases, I can´t lost the order, the order must be ASC, but I need take the last records, withount the 65,000 first.
Firstly, I take the 65,000 first records. After, I take the rest, but always in ASC order.

Some tables doesn´t have a unique primary key defined. They have more then 1 key.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-05 : 14:25:13

If you can't get around your DESC problem, then you can put your data into two tables.

SELECT *
INTO Table2
FROM Table1

SELECT TOP 65000 *
INTO TableA
FROM Table2
ORDER BY SomeColumn ASC

DELETE FROM Table2 WHERE SomeColumn IN (SELECT SomeColumn FROM TableA)

SELECT *
INTO TableB
FROM Table2

DELETE FROM Table2

DROP TABLE Table2

Now run 2 different exports using TableA and TableB

What the script does it copy all of your data into Table2. It then copies the first 65,000 rows into TableA. Then it deletes those rows from Table2. Then it copies the rest of the rows in Table2 into TableB. Then it deletes all rows from Table2. Then it drops Table2 because you don't need it anymore.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources