Fill Column with Sequence

[python1]

I apologize if this is redundant.

How would one fill an empty column with a sequence of numbers? The
column exists in a table with aproximately 1000000 rows of data. I
believe in oracle the following would work:

update foo set bar = rownum;

....but 'rownum' does not seem to exist in mssql. The numbers do not need
to be in order, but I would like to keep them somewhat small.

Any help would be appreciated.

[David Portas]

If you want to create a surrogate key (as seems to be implied by your
statement "The numbers do not need to be in order") you can add an IDENTITY
column. But perhaps you don't need to do that at all. If you explain your
actual requirement maybe we could advise you better.

--
David Portas
SQL Server MVP
--

[Aaron W. West]

try this undocumented syntax if you like

declare @seq int
set @seq = 0 -- one less than the first number
update foo set @seq = bar = @seq + 1

"python1" <py*****@spamless.net> wrote in message
news:cc*********@enews3.newsguy.com...
I apologize if this is redundant.

How would one fill an empty column with a sequence of numbers? The
column exists in a table with aproximately 1000000 rows of data. I
believe in oracle the following would work:

update foo set bar = rownum;

....but 'rownum' does not seem to exist in mssql. The numbers do not need
to be in order, but I would like to keep them somewhat small.

Any help would be appreciated.

[python1]

Aaron W. West wrote:

try this undocumented syntax if you like

declare @seq int
set @seq = 0 -- one less than the first number
update foo set @seq = bar = @seq + 1

Works perfectly. Thank you.

[Serge Rielau]

python1 wrote:

Aaron W. West wrote:

try this undocumented syntax if you like

declare @seq int
set @seq = 0 -- one less than the first number
update foo set @seq = bar = @seq + 1

Works perfectly. Thank you.

I wonder whether MS SQL Server defines these row-wise semantics.
You may ask for trouble here similar to the olden days when folks
believed GROUP BY implies ORDER BY.
Doesn't MS SQL Server provide an identity() function? This seems so much
cleaner and more partable.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Joe Celko]

>> How would one fill an empty column with a sequence of numbers? <<

Why would one fill a column with a sequence of numbers?

What does this magical PHYSICAL storage number mean in your LOGICAL data
model? Think about the basics for two seconds. How do you go to the
reality from which you derived your data model and verify it?

....but 'rownum' does not seem to exist in mssql. <<
It does not exist in the Relational Model, actually.
The numbers do not need to be in order, but I would like to keep them

somewhat small. <<

Order? Rows in a table do not have any ordering by definition; this is
set oriented language. Now if you were in a 1950 sequential file system
instead of an RDBMS in the 21-st century, that would make sense.

What are you really trying to do? You have asked how to code a solution
to some actual problem, having already decided on the answer before
telling anyone the problem. Surely, your schema is not so screwed up
that you want to use this thing for a key!!

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[python1]

Joe Celko wrote:

How would one fill an empty column with a sequence of numbers? <<

Why would one fill a column with a sequence of numbers?

What does this magical PHYSICAL storage number mean in your LOGICAL data
model? Think about the basics for two seconds. How do you go to the
reality from which you derived your data model and verify it?

....but 'rownum' does not seem to exist in mssql. <<

It does not exist in the Relational Model, actually.

The numbers do not need to be in order, but I would like to keep them

somewhat small. <<

Order? Rows in a table do not have any ordering by definition; this is
set oriented language. Now if you were in a 1950 sequential file system
instead of an RDBMS in the 21-st century, that would make sense.

What are you really trying to do? You have asked how to code a solution
to some actual problem, having already decided on the answer before
telling anyone the problem. Surely, your schema is not so screwed up
that you want to use this thing for a key!!

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Just to clear it up...

We are running a GIS database application called ArcSDE. The application
stores GIS data and uses MSSQL as a backend. Tables are created and data
imported through the application (ArcCatalog). In my case, the data
would not import into the table through the app, so it was loaded
through enterprise manager. There is one extra column in the SDE tables
named 'objectid' which runs in sequence and is used by the sde
application for keeping track of the records. After importing the data
though mangler the objectid column was 'null' because it had been set to
'ignore' in the import transform. Data would not display in the table
within ArcCatalog with this column empty. After running the query below:

declare @seq int
set @seq = 0
update foobar set @seq = objectid = @seq + 1

....the data could be displayed in the app.

The imports usually work correctly through the application, so this
measure will probably not be needed often.