473,246 Members | 1,542 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Fill Column with Sequence

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.
Jul 20 '05 #1
6 8701
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
--
Jul 20 '05 #2
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.
Jul 20 '05 #3
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.
Jul 20 '05 #4
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
Jul 20 '05 #5
>> 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!
Jul 20 '05 #6
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.
Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Ken1 | last post by:
I am going to drop a primary key from one column and create a new column to be used as primary key in an existing database. The old column was a date column which someone earlier though was a good...
3
by: D. Dante Lorenso | last post by:
I just ran into a dump/restore problem with a bigserial column on a renamed table. BIGSERIAL columns when created will automagically create the sequence also. The sequence name is derived from...
4
by: UDBDBA | last post by:
Hi: we have column with GENERATED ALWAYS AS DEFAULT. So, we can insert into this column manually and also let db2 generate a value for this column. Given a scenario, how can i find the NEXTVAL...
4
by: Sherwood Botsford | last post by:
Table Markers ID (Primary Key) This&That PointClass (Combo box) Points Table PointClasses PointClass (primary key) Points (number) Description (Text)
4
by: Mervin Williams | last post by:
I have several tables involved in my application, but the two in question here are the company and address tables. The company table has business_address_id and mailing_address_id columns, which...
30
by: Raymond Hettinger | last post by:
Proposal -------- I am gathering data to evaluate a request for an alternate version of itertools.izip() with a None fill-in feature like that for the built-in map() function: >>> map(None,...
12
by: Raymond Hettinger | last post by:
I am evaluating a request for an alternate version of itertools.izip() that has a None fill-in feature like the built-in map function: >>> map(None, 'abc', '12345') # demonstrate map's None...
10
by: dauphian | last post by:
Hello, I am new to .net and am trying to build a report application that queries 4 different tables based on a id, and I need to return them in the same table for easy viewing. Basically, I...
5
by: Veeru71 | last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS IDENTITY), is there any way to get the last generated value by DB2 for the identity column? I can't use identity_val_local() as...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.