When I create a new record in a table that has a primary key of type INT and I want to retrieve the value of its ID, I use something like this:
INSERT INTO MyTable (Fields)
VALUES (MyValues);
SELECT SCOPE_IDENTITY();
However, this does not work with primary keys of type UNIQUEIDENTIFIER. So the solution is:
INSERT INTO MyTable (Fields)
OUTPUT Inserted.MyPrimaryField
VALUES (MyValues);
Unfortunetly, this does not work with remote tables from linked servers, since I get an error reading:
A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.
Are there any other options?