How To Set Auto Increment In Sql Server Management Studio
Primary key cavalcade auto increase in existing table
How-do-you-do
I take 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 take commented out while testing:
--INSERT INTO hrsoplog (comp_id,u_version,order_no,print_dt,print_by,print_type)
--SELECT
-- CompanyCode,
-- '!',
-- OrderNumber,
-- GETDATE(),
-- 'Motorcar',
-- 'Lodge'
--FROM @Orders
hrsoplog is an existing table and has a Main Key called print_id
How do i showtime this print_id at ane and increment by 1 everytime a new row is added.
Thanks in advance
John
It is not articulate if print_id has the IDENTITY belongings. In that instance, yous need to practice null at all.
If not, y'all 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 Past (SELECT 1)) + @id, CompanyCode, '!', OrderNumber, GETDATE(), 'AUTO', 'Lodge' FROM @Orders COMMIT TRANSACTION 
How-do-you-do @JohnKelly-5410,
Welcome to the microsoft TSQL Q&A forum!
If your table is empty, yous can drop and recreate the table again and add IDENTITY(one,1) to the column definition of print_id to make your print_id cavalcade in hrsoplog table auto increase.
Something like this:
CREATE TABLE hrsoplog ( print_id int IDENTITY(1,one) NOT Zip, comp_id, int, ... ... ); SQL Server does not let you to add IDENTITY by altering a table.
If you lot have any question, please experience free to let me know.
Regards
Echo
If the reply is helpful, please click "Have Reply" and upvote it.
Notation: Please follow the steps in our documentation to enable electronic mail notifications if you want to receive the related electronic mail notification for this thread.
Cheers for all your helpful input much appreciated
The print_id IDENTITY property is False and as Echo suggested i dont seem to exist able to change this here
I have a Examination database setup and will try both solutions suggested by Echo and Erland and update the thread when this has been washed
Regards
John
I think that y'all did not try setting the IDENTITY property manually besides, only using the suggested Table Designer instead of manual scripts.
0 Votes 0 ·
Hi Erland
But 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 Past (SELECT i)) + @id,
CompanyCode,
'!',
OrderNumber,
GETDATE(),
'AUTO',
'Club'
FROM @Orders
are you lot proverb to supervene upon this with your code?
--INSERT INTO hrsoplog (comp_id,u_version,order_no,print_dt,print_by,print_type)
--SELECT
-- CompanyCode,
-- '!',
-- OrderNumber,
-- GETDATE(),
-- 'Automobile',
-- 'Order'
--FROM @Orders
Yes. Don't forget the wrapping BEGIN and COMMIT TRANSACTION. They are needed to preclude errors if there are simultaneous updates.
0 Votes 0 ·
Hello
I lost remote connection to my function PC on Fri hence the delay in trying out your solutions. I am trying Erland solution start and getting an error that the isnull function needs ii arguements?
SELECT @id = isnull(MAX(print_id, 0) FROM hrsoplog WITH (UPDLOCK)
Regards
John
SELECT @id = isnull(MAX(print_id, 0) FROM hrsoplog WITH (UPDLOCK)
The endmost brace for the MAX function is missing =>
SELECT @id = isnull(MAX(print_id), 0) FROM hrsoplog WITH (UPDLOCK)
Many thanks to everyone who helped with this. A keen resource to use.
I tried the solution put forward past Erland and it works perfect.
How To Set Auto Increment In Sql Server Management Studio,
Source: https://docs.microsoft.com/answers/questions/508265/primary-key-column-auto-increment-in-existing-tabl.html
Posted by: richardsonaloons.blogspot.com
Is it an existing table that already contains some rows having print_id values?
0 Votes 0 ·
Hi
Its an existing table that has no data in it yet so therefore no print_id values. Hither is output from select all
0 Votes 0 ·
Open the Design of hrsoplog table in Management Studio, select the print_id column, change the Identity Specification, (Is Identity) to "Yes", salve the changes (printing <Ctrl+Due south>). Practice not specify print_id in insert operations; information technology volition be incremented automatically.
0 Votes 0 ·
Could you accept any updates?
0 Votes 0 ·