Getting the primary key of an newly inserted row in SQL Server 2008

If you’re inserting a whole set of rows, selecting the SCOPE_IDENTITY() won’t do. And SCOPE_IDENTITY also only works for (numeric) identity columns – sometimes your PK is something else…

But SQL Server does have the OUTPUT clause – and it’s very well documented on MSDN!

INSERT INTO dbo.Table(columns)
OUTPUT INSERTED.p_key, INSERTED.someothercolumnhere .......
VALUES(...) 

Those values will be “echoed” back to the calling app, e.g. you’ll see them in a grid in SQL Server Management Studio, or you can read them as a result set from your C# or VB.NET calling this INSERT statement.