How to increment record id in an insert stored procedure

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 th
e
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, instea
d
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 TransactionLook up IDENTITY in Books Online, that does all the work for you.
Jacco Schalkwijk
SQL Server MVP
"pelican" <pelican@.discussions.microsoft.com> wrote in message
news:765A9768-316A-4446-82A6-58737F5040CB@.microsoft.com...
> 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
>