0

I am trying to merge records from my local database to remote database with insert and update but I'm getting

Invalid object name [localdatabase].dbo.MASTER_CORPORATECOMPANY*** error.

I have shared the code below. Can anyone tell me how can I achieve the same?

EXEC('MERGE INTO [linkserver].[remotedatabase].dbo.MASTER_CORPORATECOMPANY AS D
USING [localdatabase].dbo.MASTER_CORPORATECOMPANY AS S
        ON D.IDENTIFICATIONNUMBER = S.IDENTIFICATIONNUMBER
WHEN MATCHED THEN
    UPDATE SET
      D.DATEOFINCORPORATION = S.DATEOFINCORPORATION,
      D.COMPANYNAME = S.COMPANYNAME,
      D.COMPANYSTATUS = S.COMPANYSTATUS,
      D.REGISTEREDSTATE = S.REGISTEREDSTATE,
      D.REGISTEREDOFFICE = S.REGISTEREDOFFICE,
      D.CREATEDDATE = S.CREATEDDATE
WHEN NOT MATCHED THEN 
      INSERT (DATEOFINCORPORATION, COMPANYNAME, COMPANYSTATUS,REGISTEREDSTATE,REGISTEREDOFFICE,CREATEDDATE)
      VALUES (S.DATEOFINCORPORATION, S.COMPANYNAME,S.COMPANYSTATUS,S.REGISTEREDSTATE,S.REGISTEREDOFFICE, S.CREATEDDATE);') AT [linkserver]
TT.
  • 15,774
  • 6
  • 47
  • 88
user2236493
  • 45
  • 1
  • 1
  • 4
  • Don't use merge. Use UPDATE/INSERT statements instead. You should avoid using merge at all. Merge statements are quite hard to debug + it has quite a lot not resolved bugs. And also it usually does not have any performance benefits against separate UPDATE/INSERT statements – Dmitrij Kultasev Oct 06 '17 at 08:15

1 Answers1

0

According to T-SQL Merge statement Syntax, the Target table can not be a remote table name. Hence, probably you need to send your local table to your remote server and execute an already created procedure on the remote server that merge the two tables, i.e. the remote master and copied temp.

Ahmed Saeed
  • 831
  • 7
  • 12