If I issue an insert/output TSQL statement, how do I capture the output clause in PowerShell?

Question

So I have a small Powershell script that is opening an Excel file for import into MSSQL.
Because of the MSSQL schema, I'll need to insert the data in pieces, capture the inserted ID, and use that ID in future insert statements.

So, if I have my first statement e.g. "INSERT INTO xx (..) OUTPUT inserted.ID [...]", how, in Powershell, do I capture the results of that output clause?

Currently using:
$cmd=new-object system.Data.SqlClient.SqlCommand($sql,$conn)
return $cmd.ExecuteNonQuery()

but I want to capture the output clause, so my default will likely change to whatever is needed for this capture.

Answer 1

return $cmd.ExecuteNonQuery()

Instead of ExecuteNonQuery(), you can use the ExecuteScalar() to get the first column of the first row returned in the result set. This will work for single-row inserts with only the identity column in the OUTPUT clause.

return $cmd.ExecuteScalar()