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

Hi @John Kelly ,

Welcome to the microsoft TSQL Q&A forum!

If your table is empty, you can drop and recreate the table again and add IDENTITY(1,1) to the column definition of print_id to make your print_id column in hrsoplog table auto increment.

Something like this:

CREATE TABLE hrsoplog  
(  
print_id int IDENTITY(1,1) NOT NULL,  
comp_id, int,  
...  
...  
);  

SQL Server does not allow you to add IDENTITY by altering a table.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Thanks for all your helpful input much appreciated

The print_id IDENTITY property is False and as Echo suggested i dont seem to be able to change this here

I have a TEST database setup and will try both solutions suggested by Echo and Erland and update the thread when this has been done

Regards

John

Answer 4

Hi Erland

Just reading over your solution again:

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

are you saying to replace this with your code?

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

Answer 5

Hi

I lost remote connection to my office PC on Friday hence the delay in trying out your solutions. I am trying Erland solution first and getting an error that the isnull function needs two arguements?

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

Regards

John