Row numbers on export

HI

I have written a script to export data from customer table to another crm package, on the export they require the first column to be numbered 1 - .... say 1000 or how ever many rows there will be.

Is it possible?

Thanks

Rich

Are you talking about a batch count? Or does every row have to have a number?

If it's batch count then use DTS add a global variable, assign the count, then use the file system object to insert the count into the file after the export

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Okay after reading it again I think you want count for each row

In sql 2005 you can use ROW_NUMBER in 2000 you can use IDENTITY with a temp table

2000 version

SELECT IDENTITY(INT, 1,1) AS Rank ,*
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT * FROM YourTable
ORDER BY SomeColumn

SELECT * FROM #Ranks ORDER BY Rank

2005 version

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn) AS 'rownumber',*
FROM YourTable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

is there not a function i could use in the statement? , i am not going to use dts

thanks

|||

do a select count(*) from (your query here)

union all

your original query

example in pubs on SQL server 2000

select convert(varchar(30),count(*)), '','','','','','','',''
from authors
union all
select au_id, au_lname, au_fname, phone, address, city, state, zip, contract
from authors

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks Denis, i will go the temp table route