Primary key column auto increment in existing table

Question

Hi

I have a Stored procedure (part shown) with the following:

DECLARE @Orders table (CompanyCode CHAR(3),OrderNumber int)
INSERT INTO @Orders(CompanyCode,OrderNumber)

At the end of the SP i have the following that i have commented out while testing:

--INSERT INTO hrsoplog (comp_id,u_version,order_no,print_dt,print_by,print_type)
--SELECT
-- CompanyCode,
-- '!',
-- OrderNumber,
-- GETDATE(),
-- 'AUTO',
-- 'Order'
--FROM @Orders

hrsoplog is an existing table and has a Primary Key called print_id

How do i start this print_id at 1 and increment by 1 everytime a new row is added.

Thanks in advance

John

Answer 1

It is not clear if print_id has the IDENTITY property. In that case, you need to do nothing at all.

If not, you can do:

BEGIN TRANSACTION

SELECT @id = isnull(MAX(print_id, 0) FROM hrsoplog WITH (UPDLOCK)

INSERT INTO hrsoplog (print_id, comp_id,u_version,order_no,print_dt,print_by,print_type)
SELECT row_number() OVER(ORDER BY (SELECT 1)) + @id, 
 CompanyCode,
 '!',
 OrderNumber,
 GETDATE(),
 'AUTO',
 'Order'
FROM @Orders

COMMIT TRANSACTION

Answer 2

SELECT @id = isnull(MAX(print_id, 0) FROM hrsoplog WITH (UPDLOCK)

The closing brace for the MAX function is missing =>

SELECT @id = isnull(MAX(print_id), 0) FROM hrsoplog WITH (UPDLOCK)

Answer 3

Many thanks to everyone who helped with this. A great resource to use.

I tried the solution put forward by Erland and it works perfect.