[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

incrementing the record number

childofthe1980s

3/28/2007 8:24:00 PM

Hello:

I am using the INSERT INTO....VALUES script in order to insert records of
fields into a table, of course.

If one of the fields of data that I want to insert is a number and it is
always going to be 1 greater than the same field in the previous record, what
sort of code do I use? Let me be more clear.

Let's say my script is:
INSERT INTO table (this, that, 24)
VALUES (dog, cat, 'one number greater than 24')

In other words, the last value in VALUES above needs to be some sort of
programming that says "insert in this field one number greater than that of
the previous record".

How do I do that?

Thanks!

childofthe1980s
6 Answers

Anith Sen

3/28/2007 8:31:00 PM

0

Lookup identity columns in SQL Server Books Online. You can set the
increment to 1 to achieve what you are trying to do.

--
Anith


childofthe1980s

3/28/2007 9:00:00 PM

0

Well, I don't want to change a column's propert yin the table or mess with
the table's properties. I just want to do some programming that says for
this record add 1 to the previous record field.

Please help!

childofthe1980s

"Raymond D'Anjou" wrote:

> Anith,
>
> Are you saying that with Identity, the next insert will generate a number
> that will always be 1 greater than the previous insert.
>
> Shame on you... :-)
>
> "Anith Sen" <anith@bizdatasolutions.com> wrote in message
> news:%23sNMRgXcHHA.4976@TK2MSFTNGP03.phx.gbl...
> > Lookup identity columns in SQL Server Books Online. You can set the
> > increment to 1 to achieve what you are trying to do.
> >
> > --
> > Anith
> >
>
>
>

Aaron [SQL Server MVP]

3/28/2007 9:07:00 PM

0

> Well, I don't want to change a column's propert yin the table or mess with
> the table's properties. I just want to do some programming that says for
> this record add 1 to the previous record field.

How do you determine which row is the "previous" row? Remember, a table is
an unordered set of rows, and unless you are using an IDENTITY column or a
DATETIME column with a default value of CURRENT_TIMESTAMP, SQL Server is not
going to have any way to tell you which row was inserted last...


childofthe1980s

3/28/2007 9:18:00 PM

0

So, basically then, there is no way to do it? That's not good. Oh, well.

childofthe1980s

"Aaron Bertrand [SQL Server MVP]" wrote:

> > Well, I don't want to change a column's propert yin the table or mess with
> > the table's properties. I just want to do some programming that says for
> > this record add 1 to the previous record field.
>
> How do you determine which row is the "previous" row? Remember, a table is
> an unordered set of rows, and unless you are using an IDENTITY column or a
> DATETIME column with a default value of CURRENT_TIMESTAMP, SQL Server is not
> going to have any way to tell you which row was inserted last...
>
>
>

Raymond D'Anjou

3/28/2007 9:36:00 PM

0

Anith,

Are you saying that with Identity, the next insert will generate a number
that will always be 1 greater than the previous insert.

Shame on you... :-)

"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:%23sNMRgXcHHA.4976@TK2MSFTNGP03.phx.gbl...
> Lookup identity columns in SQL Server Books Online. You can set the
> increment to 1 to achieve what you are trying to do.
>
> --
> Anith
>


Andrew J. Kelly

3/29/2007 2:25:00 AM

0

Without seeing the actual DDL and values none of us can say that. But Aaron
is 100% correct in that you need a way to know logically which row is which
and in what order. If this is a requirement you may have to alter the table
to get the desired result.

--
Andrew J. Kelly SQL MVP

"childofthe1980s" <childofthe1980s@discussions.microsoft.com> wrote in
message news:8AC799E0-15C4-4CBF-97E2-DD6C9CBC2B0B@microsoft.com...
> So, basically then, there is no way to do it? That's not good. Oh, well.
>
> childofthe1980s
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
>> > Well, I don't want to change a column's propert yin the table or mess
>> > with
>> > the table's properties. I just want to do some programming that says
>> > for
>> > this record add 1 to the previous record field.
>>
>> How do you determine which row is the "previous" row? Remember, a table
>> is
>> an unordered set of rows, and unless you are using an IDENTITY column or
>> a
>> DATETIME column with a default value of CURRENT_TIMESTAMP, SQL Server is
>> not
>> going to have any way to tell you which row was inserted last...
>>
>>
>>