0

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?

Osprey
  • 1,523
  • 8
  • 27
  • 44
  • 1
    Is the GUID autogenerated with a `NEWID()` default? If so, you can simply generate it locally instead and insert it explicitly. Being able to generate a GUID anywhere and still have it be unique is half of the appeal. – Jeroen Mostert Nov 07 '19 at 10:36
  • this might help https://stackoverflow.com/questions/18981229/inserted-id-in-linked-sql-server – Shikhar Arora Nov 07 '19 at 10:36
  • If it's a `CLUSTERED` Primary key, however, @JeroenMostert I don't recommend `NEWID()`; you should use `NEWSEQUENTIALID()` and you won't be able to do that. – Thom A Nov 07 '19 at 10:57
  • Are you only inserting a single row? If so, have you considered building an SP on the remote server with an `OUTPUT` parameter? – Thom A Nov 07 '19 at 10:58
  • Thanks @JeroenMostert that is exactly what I needed to do and it works like a charm. Also the simplest to implement. – Osprey Nov 07 '19 at 12:29
  • Do note that @Larnu's comment is apt -- from a performance point of view a GUID is not a suitable choice for a clustered index (even one using `NEWSEQUENTIALID` I would at best call "not as bad"), regardless of whether you need it as a key. On a small table the differences don't matter, but as the table grows larger it does. That's a [separate discussion](https://stackoverflow.com/q/11938044/4137916), though. – Jeroen Mostert Nov 07 '19 at 13:19

0 Answers0