0

So, I have a data set on server "A" and two tables ("t1", "t2") on linked server "B". I cannot modify server "B" schema. What I am trying to achieve is: insert first two data set (non-identity) columns into [B].[t1] which should generate identity index value, and then add second two, also non-identity, columns into [B].[t2], which contains column MsgId that should map to corresponding row in [B].[t2]. A visual version of how it works

It seems that I cannot use OUTPUT nor SCOPE_IDENTITY() on a linked server (both return nothing).

My question is so: Is there a way to add data from server A to server B without modifying latter?

My current solution involves cursors and creating a stored procedure on a remote server, but I feel like I am doing it completely wrong and reinventing wheel in the process. Also, modifying database structure is a painful process involving lots of paperwork.

keeborgue
  • 1
  • 1
  • If you can create stored procedures on the linked server, I think that [my answer here](https://stackoverflow.com/a/38217498/3094533) can help you solve your problem. – Zohar Peled Dec 06 '19 at 08:46

1 Answers1

0

What you want to do is ETL (Extract, transform, load) and using a tool like SSIS helps to do that much cleaner and more maintainable.

Shahram Shobeiri
  • 90
  • 7