Calculate/create Row Number without identity

[chrispycrunch]

How do I output a row number for a table solely for the purpose of
querying for a unique row?

In my problem, the table from a legacy system does not have a primary
key, so it limits various querying I'd like to do that identifies
uniqueness in the table.

The problem is that since I'm using DTS to simply copy the table to
SQL, I don't want to create identity rows.

[Erland Sommarskog]

chrispycrunch (ch***********@gmail.com) writes:

How do I output a row number for a table solely for the purpose of
querying for a unique row?

In my problem, the table from a legacy system does not have a primary
key, so it limits various querying I'd like to do that identifies
uniqueness in the table.

The problem is that since I'm using DTS to simply copy the table to
SQL, I don't want to create identity rows.

Not sure why the use of DTS would preclude the use of an IDENTITY row,
but then again I have no experience of DTS. After all, IDENTITY seems
perfect in this case.

If there really is a problem for DTS, you make a two-stepper and have
DTS to park the data in a transient table, and insert from there into
the target table.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[joeNOSPAM]

Hi. Are you sure that the original table has no duplicates? If there
are any
duplicate, you do not have unique rows. However, assuming you want to
establish an identity for future use, you could extract the old data
row-by-row
and set a unique identity of your own construction, and maintain that
with
any new rows that get added (and make sure to have a unique index on
that
value.
All in all, the identity column is made for this.
Joe Weinstein at BEA