troubleshooting Question

Returning Identity key from a linked server

Avatar of sherbug1015
sherbug1015Flag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
4 Comments1 Solution3212 ViewsLast Modified:
I am doing an insert from one server to another linked server.  I need to have the Identity Key returned to me from the linked server.

My statement looks like this:

DECLARE @newids TABLE (id INT)

INSERT  INTO LinkedServer.LinkedDB.dbo.LinkedTable
        ( Column1 ,
          Column2  
         
        )
       OUTPUT INSERTED.MyNewid INTO @newids(id)
        SELECT  Column1 ,
                Column2
               
        FROM    LocalDB.dbo.localtable

I am getting this error:

A remote table cannot be used as a DML target in a statement which includes an OUTPUT clause or a nested DML statement.


Does anyone know how I can solve this issue.

Thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros