Insert into SQL and get primary key

PowerShell Help
1

I am looking to add a basic inventory script to our PC’s and write it to SQL.
I can already add to the SQL DB using something like this but don’t know to “fetch” the primary key of the record I just inserted:

$RetiredUpdatedataSource = "SQLSERVER" $RetiredUpdateuser = "sa" $RetiredUpdatepwd = "MYPASSWORD" $RetiredUpdatedatabase = "RetiredPCs" $RetiredUpdateconnectionString = "Server=$RetiredUpdatedataSource;uid=$RetiredUpdateuser; pwd=$RetiredUpdatepwd;Database=$RetiredUpdatedatabase;Integrated Security=False;" $RetiredUpdatequery = "Insert into tblRetiredPCs (ComputerName, Serial, Model, Location, LastLogin, OU, DateRetired) VALUES ('$PCName', '$RetiredSerial', '$RetiredModel', '$RetiredLocation', '$RetiredlastLoggedIn', '$RetiredOU', '$RetiredDate')" $RetiredUpdateconnection = New-Object System.Data.SqlClient.SqlConnection $RetiredUpdateconnection.ConnectionString = $RetiredUpdateconnectionString #$connection.ConnectionString = "Server=$dataSource;Database=$database;Integrated Security=True;" $RetiredUpdateconnection.Open() $RetiredUpdatecommand = $RetiredUpdateconnection.CreateCommand() $RetiredUpdatecommand.CommandText = $RetiredUpdatequery $RetiredUpdateresult = $RetiredUpdatecommand.ExecuteReader() $Global:RetiredUpdatetable = new-object "System.Data.DataTable" $RetiredUpdatetable.Load($RetiredUpdateresult) $RetiredUpdateconnection.Close()

I wanted to write that “key” number to a text file on the PC and maybe even store it in AD with the computer object but don’t know how to retrieve it at the time the record is inserted.

Thanks,
Scott

2

The primary key would be a known column in your table such as the hostname of your server, why fetch it?

3

I was planning on using a designated “Number” column that is marked as (Is Identity) and would increment with each record that was added.
I was going to use the PCNAME as the key and there should never be any duplicates but just in case I decided to use the “Number” column.

Does that make sense?

Scott

4

In an AD environment I’d use hostname as there can only be one.

5

I agree but in our environment we may be keeping track of PC’s with the same name which are not just in AD without getting into too much detail. In this scenario we would need some other identifier which is why I was trying to capture the number.

Is there no way to do this?

Thanks for your help,
Scott

6

I’ve not tested it but this Stack Overflow answer suggests using an OUTPUT clause as part of your query. Should work for SQL Server 2005 or newer:

$RetiredUpdatequery = "Insert into tblRetiredPCs (ComputerName, Serial, Model, Location, LastLogin, OU, DateRetired) OUTPUT (Inserted.PrimaryKey) VALUES('$PCName', '$RetiredSerial', '$RetiredModel', '$RetiredLocation', '$RetiredlastLoggedIn', '$RetiredOU', '$RetiredDate')"

There are a few other solutions in that thread but this looks the most appropriate.

7

This worked perfectly using OUTPUT (Inserted.Number) then call it using $RetiredUpdatetable.Number

Thanks so much!!