Link to home
Create AccountLog in
Avatar of abuhaneef
abuhaneefFlag for United States of America

asked on

Auto Increment the PersonID field using Invoke-Sqlcmd in Powershell

I have a non Primary Key PersonID in person. How do I auto increment the PersonID field using Invoke-Sqlcmd in Powershell.  

INSERT INTO person (firstname, lastname, title, entityid, workgroupid, supervisorid, did, BirthDate, HireDate, type, site, remote, email, HRID) VALUES ('Trey', 'Songz', 'Manager - Customer Support', '308', '272', '4691', '610-504-6644', '12/12/1977 00:00:00', '04/20/2020 00:00:00', 'Employee', 'Denver, CO', '1', 'trey.songz@verizon.com', '6700');
Avatar of lcohan
lcohan
Flag of Canada image

Can you check to see your table structure? Maybe the PersonID column is IDENTITY already which means any new row inserted in the table will automatically get a new id.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver15

Without having the IDENTITY column property set in SQL database table/column it would be much more difficult to do this via your own SQL code due to the need of an atomic transaction not easy to get under concurency.
First check your current table structure as lcohan recommended.
If you are sure that PersonID column has no set IDENTITY property then you can add rows with the following syntax:
INSERT INTO Person.person(PersonID, firstname, lastname, title, entityid, workgroupid, supervisorid, did, BirthDate, HireDate, type, site, remote, email, HRID) 
VALUES ((select max(PersonID) from Person.person with(updlock, tablock)), 'Trey', 'Songz', 'Manager - Customer Support', '308', '272', '4691', '610-504-6644', '12/12/1977 00:00:00', '04/20/2020 00:00:00', 'Employee', 'Denver, CO', '1', 'trey.songz@verizon.com', '6700'); 

Open in new window


Avatar of abuhaneef

ASKER

The IDENTITY property is false.  
((select max(PersonID) from Person.person with(updlock, tablock)) does not increment.

I ended up doing the following and using $maxP.

$maxP = (Invoke-Sqlcmd -Query "SELECT MAX(PersonID) FROM person WITH(updlock,tablock);" -ServerInstance $SQLServer -Database $DataBase -Username $UserName -Password $DBPass | Select-Object -ExpandProperty Column1) + 1
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
You could use ROW_NUMBER()