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');
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');
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:
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'); 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
((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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could use ROW_NUMBER()
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.