Insert into SQL and get primary key

This topic contains 6 replies, has 3 voices, and was last updated by Profile photo of Scott Windmiller Scott Windmiller 9 months, 2 weeks ago.

  • Author
    Posts
  • #54966
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #54969
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

    • #54970
      Profile photo of Scott Windmiller
      Scott Windmiller
      Participant

      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

  • #54974
    Profile photo of Dan Potter
    Dan Potter
    Participant

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

  • #54979
    Profile photo of Scott Windmiller
    Scott Windmiller
    Participant

    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

  • #54982
    Profile photo of Matt Bloomfield
    Matt Bloomfield
    Participant

    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.

    • This reply was modified 9 months, 2 weeks ago by Profile photo of Matt Bloomfield Matt Bloomfield. Reason: Clarity
    • #54985
      Profile photo of Scott Windmiller
      Scott Windmiller
      Participant

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

      Thanks so much!!

You must be logged in to reply to this topic.