SELECT & number of line -- sql server misc

myname
4/5/2004 12:21:37 PM

Hello,

I'm using SQL Server 2000.

I would like each line of my SELECT to contain a column
giving the number of the current line.

It seems the only way to do that is something like:

INSERT identity(int,1,1) as lineNumber,* INTO newTable FROM oldTable

But oldTable as an identity column already... so I get error #8108...

Can I do without "INSERT INTO" ? If not, how can I solve my identity
conflict error ?

Thanks a lot for any help.

Bob Simms
4/5/2004 12:27:28 PM

[quoted text, click to view]

Define your new table first with an Identity col for your rownumber column
(but not an Identity for the old Identity column).

then

INSERT NewTable
SELECT * FROM OldTable.

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004

sql server misc : SELECT & number of line