This topic contains 6 replies, has 3 voices, and was last updated by
October 5, 2016 at 1:12 pm #54966
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;"
$RetiredUpdatecommand = $RetiredUpdateconnection.CreateCommand()
$RetiredUpdatecommand.CommandText = $RetiredUpdatequery
$RetiredUpdateresult = $RetiredUpdatecommand.ExecuteReader()
$Global:RetiredUpdatetable = new-object "System.Data.DataTable"
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.
October 5, 2016 at 1:56 pm #54969ParticipantPoints: 21Rank: Member
The primary key would be a known column in your table such as the hostname of your server, why fetch it?
October 5, 2016 at 1:59 pm #54970
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?
October 5, 2016 at 2:49 pm #54974ParticipantPoints: 21Rank: Member
In an AD environment I'd use hostname as there can only be one.
October 5, 2016 at 4:24 pm #54979
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,
October 5, 2016 at 5:01 pm #54982ParticipantPoints: 41Rank: Member
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.
October 5, 2016 at 7:24 pm #54985
This worked perfectly using OUTPUT (Inserted.Number) then call it using $RetiredUpdatetable.Number
Thanks so much!!
The topic ‘Insert into SQL and get primary key’ is closed to new replies.