Update, Insert or delete data from table on another server

Question

Post reply

Update, Insert or delete data from table on another server

pwalter83

SSChampion

Points: 14575
More actions
July 26, 2011 at 3:36 am

#254851

Hi,
I am trying to create a code to either insert, update or delete data from a table on a different server. whenever some date is changed on a table in server 1, the corresponding table on server 2 should be updated accordingly.
Below is the code I am trying to create but the problem I get is I am unable to compare the 2 tables which exist in 2 different servers:
-----------------------------
Select *
Into #TempA
From TB_ARTICLE
Where ARTICLE_ID not in (Select ARTICLE_ID from KEULONSV105..website.TB_ARTICLE)
set identity_insert TB_ARTICLE on
Insert into TB_ARTICLE
Select * From #TempA
-----------------------------
Any hint or suggestion will be appreciated.
Regards,
Paul

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looks like a good place for replication.

Have you checked that option?

-- Gianluca Sartori

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Gianluca Sartori (7/26/2011)
Looks like a good place for replication.
Have you checked that option?

Actually this option has been discussed before and rejected by the IT support team here due to some reasons. Is it possible to just give me some ideas to change the code mentioned ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

This should do:

Select *

Into #TempA

From TB_ARTICLE

Where ARTICLE_ID not in (Select ARTICLE_ID from remote_server_name.database_name.schema_name.TB_ARTICLE)

EXEC('USE database_name; set identity_insert TB_ARTICLE on') AT remote_server_name

Insert into remote_server_name.database_name.schema_name.TB_ARTICLE

Select * From #TempA

Hope this helps

Gianluca

-- Gianluca Sartori

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Gianluca Sartori (7/26/2011)
This should do:
Select *
Into #TempA
From TB_ARTICLE
Where ARTICLE_ID not in (Select ARTICLE_ID from remote_server_name.database_name.schema_name.TB_ARTICLE)
EXEC('USE database_name; set identity_insert TB_ARTICLE on') AT remote_server_name
Insert into remote_server_name.database_name.schema_name.TB_ARTICLE
Select * From #TempA
Hope this helps
Gianluca

Hi Gianluca,

Thanks for your reply and solution, however, when I tried your code, I got the following error:

'Invalid object name 'KEULONSVDSQL1.WEBSITE.TB_ARTICLE'. I think its due to the fact that the server name- KEULONSVDSQL1 is not an object.

Below is the code that I tried:

Select *

Into #TempA

From TB_ARTICLE

Where ARTICLE_ID not in (Select ARTICLE_ID from KEULONSVDSQL1.WEBSITE.TB_ARTICLE)

EXEC('USE WEBSITE; set identity_insert TB_ARTICLE on') AT KEULONSVDSQL1

Insert into KEULONSVDSQL1.WEBSITE.TB_ARTICLE

Select * From #TempA

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Did you register the linked server?

-- Gianluca Sartori

pwalter83 SSChampion Points: 14575 More actions · Answer 6

Gianluca Sartori (7/26/2011)
Did you register the linked server?

I have a couple of questions now based on your question :

1. Does the other server need to be registered ?

2. How do I know that the other server is linked ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

1. Does the other server need to be registered ?
2. How do I know that the other server is linked ?

1. Yes. Use sp_addlinkedserver or SSMS wizard (server objects, linked servers --> right click, add new linked server).

2. select * from sys.servers should return the linked server name and connection details

-- Gianluca Sartori