Insert into linkedserver from local table

Question

I am trying to insert some data from my local table into linkedserver via sql server. this is what i am doing but it keeps on throwing syntax error.

TRY 1

EXEC(
  'INSERT into test.testschema.testoperation 
    (viasatsubscriptionID, subscriptionrowdate, phonenumberday, viasatcustomerid)
  SELECT * FROM rdata.dbo.testoperation'
)AT REDSHIFT64 

TRY 2

EXEC(
  'INSERT into test.testschema.testoperation 
  (viasatsubscriptionID, subscriptionrowdate, phonenumberday, viasatcustomerid)'
)AT REDSHIFT64 

SELECT * FROM rdata.dbo.testoperation

Both fails.

Any thoughts where i am going wrong?

Answer 1

testoperation is your local table, and since your query runs on the remote server, the table does not exist.

Why don't you try inserting directly to the remote table:

INSERT into REDSHIFT64.test.testschema.testoperation 
    (viasatsubscriptionID, subscriptionrowdate, phonenumberday, viasatcustomerid)
  SELECT * FROM rdata.dbo.testoperation