incrementing a counter in sql

20 Jul 2006 4:44 PM

Kelly

I need to create a table with two values - old no and new no
the old no should come from an existing table and the new number should begin with a starting value and increment by 1

how do i do this???? From http://www.developmentnow.com/g/113_0_0_0_0_0/sql-server-programming.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com

20 Jul 2006 5:02 PM

Arnie Rowland

The easiest way is to define one column as IDENTITY, and set its starting value.

For example,

CREATE TABLE NewTable
   (    NewNumber   int   IDENTITY(1000,1)
      , OldNumber   int
   )

Use whatever starting number you desire instead of the 1000 above. Of course, int (integer) datatype is limited to a maximum value of: 2,147,483,647. If your numbers will grow larger then use bigint or some other appropriate data type.

--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Kelly" <nospam@developmentnow.com> wrote in message news:d739fd1c-33d5-41d4-a55e-7b0da3c48cf6@developmentnow.com...
>I need to create a table with two values - old no and new no
> the old no should come from an existing table and the new number should begin with a starting value and increment by 1
>
> how do i do this????
>
> From http://www.developmentnow.com/g/113_0_0_0_0_0/sql-server-programming.htm
>
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

22 Jul 2006 4:08 PM

kelly

Thanks, Arnie. I knew it had to be something easy, I just could not find it. You have saved me lots of time! From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=113&threadid=791161 Posted via DevelopmentNow.com Groups http://www.developmentnow.com

22 Jul 2006 10:25 PM

--CELKO--

>> I need to create a table with two values - old number and new number the old number should come from an existing table and the new number should begin with a starting value and increment by 1 <<

Generally, we prefer "nbr" as the affix for "number" so it does not
conflict the English word "No" and matches the more international
Latin-based "numero", "numbero", etc. and common abbreviation. Hey, I
wrote a book on progamming style and spent weeks on nit-picky things
like this to save you from it.

A motre serious question is: What the heck does this mean in your data
model? What is the transformation rule?

a) Order the old numbers and re-number? ASC or DESC?
b) Random physical order, so we know you have no data model !
c) a formula that you did not give in the lack of specs you posted?

Frankly, this sounds like a kludge to clean up something. Can you tell
us what you are actually trying to do? Or tell us that all you want is
the fastest, dirty, stinking proprietary kludge you can find, so we
will not waste time solving the real problems.