title: Inserted Id in linked SQL Server
body: <p>I have linked together 2 Microsoft SQL servers, let's call them LinkedServer1 and Server2. I am trying to insert data from the second one using an INSERT script with OUTPUT to obtain the auto-incremented id:</p>
  
  <pre><code>DECLARE @newIdHolderTable TABLE (Id INT)
  
  INSERT INTO [LinkedServer1].[Db1].[dbo].[Table1]
    (Field1
    ,Field2)
  OUTPUT inserted.ID INTO @newIdHolderTable 
  SELECT 
      Field1
     ,Field2
  FROM [Server2].[Db1].[dbo].[Table1]
  WHERE [Id] = @Id
  </code></pre>
  
  <p>Unfortunately this fails with an error message: </p>
  
  <blockquote>
    <p>A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.</p>
  </blockquote>
  
  <p>How can I insert values into a linked SQL server table and obtain the new Id?</p>