How to increment record id in an insert stored procedure

From: Peter The Spate (anonymous_at_discussions.microsoft.com)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 08:37:32 -0800

My first though is that your doing this wrong (sorry).
Within SQL server there is a facility to have
an 'Identity' column which will automatically add one to
your recordnum fields without the SQL that you have
written, but on with the rest of it.

The second problem with your code is that the @recordcount
is not being incremented meaning you will get the same
value.

With you code I would sugest the following change

Insert into [dbo].[table1] (recordnum, field1, field2,
field3, field4, field5, field6)

Select (Select Max(recordnum) + 1 from [dbo].[table1]),
field1, field2, field3, field4, field5, field6 from [dbo].
[table2]

However please look up identity columns, it will help you
honest ;)

Peter

"A politician needs the ability to foretell what is going
to happen tomorrow, next week, next month, and next year.
And to have the ability afterwards to explain why it
didn't happen."
Winston Churchill

>-----Original Message-----
>Hello everyone,
>
>I have a problem. I need to insert records from table2
to table1. In
>table1 there is a record id which serves as the key of
the table. Each time
>a record needs to be inserted, a record id is created.
This record id is the
>max(recordid)+1. I wrote the following stored procedure
to insert records.
>This stored procedure will insert the ten records from
table2 into table1.
>The only problem is that the recordcount is same for all
ten records, instead
>of incrementing. Should I store the table id in a
different table? Any
>response will be greatly appreciated.
>
>Begin Transaction
> (Select @recordcount = (max(record)+1) from [dbo].
[table1])
>Insert into [dbo].[table1] (recordnum, field1, field2,
field3, field4,
>field5, field6)
>Select @recordcount, field1, field2, field3, field4,
field5, field6 from
>[dbo].[table2]
>
> select @errnum=@@Error, @RowCount = @@RowCount
> if @errnum <> 0 GOTO sqlerror
>
>Commit Transaction
>
>.
>

Relevant Pages