Using Distributed queries, You can select,insert,update delete data from other server(oracle, Mysql, sqlserver)

In previous post, we saw how to create linked server and how to select data with these linked server.To day I will try to show some more interesting things we can do this distributed queries.
When I select data with distributed queries, I thought why don't we insert,delete, update data in linked server. SQL server providing Pass-through queries to achieve this functionality.

---TESTING WITH MY ORACLE INSTANCE

---SO I CREATED ONE TESTING TABLE IN MY ORACLE INSTANCE NAME CALLED "TEST".

--FIRST CREATE LINKED SERVER WITH ALL THE CREDENTIALS

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'ORACLE_REMOTE', @SRVPRODUCT=N'ORACLE', @PROVIDER=N'ORAOLEDB.ORACLE', @DATASRC= 'INSTANCE NAME'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'ORACLE_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XX'

---EXECUTING SELECT QUERY

SELECT * FROM OPENQUERY(ORACLE_REMOTE,'SELECT ID,NAME FROM SHEMA_NAME.TEST')

-----EXECUTING INSERT PASS-THROUGH QUERY

INSERT OPENQUERY(ORACLE_REMOTE,'SELECT ID,NAME FROM SHEMA_NAME.TEST')

VALUES(1,'BHASKER')

-----EXECUTING UPDATE PASS-THROUGH QUERY

UPDATE OPENQUERY(ORACLE_REMOTE,'SELECT NAME FROM SHEMA_NAME.TEST WHERE ID=1')

SET NAME='BHASKER_NEW'

-----EXECUTING DELTE PASS-THROUGH QUERY

DELETE OPENQUERY(ORACLE_REMOTE,'SELECT NAME FROM SHEMA_NAME.TEST WHERE ID=1')

---TESTING WITH MY SQL SERVER INSTANCE

USE MASTER

EXEC MASTER.DBO.SP_ADDLINKEDSERVER @SERVER = N'SQL_REMOTE', @SRVPRODUCT=N'SQL', @PROVIDER=N'SQLOLEDB', @DATASRC=N'SQLINSTANCE'

EXEC MASTER.DBO.SP_ADDLINKEDSRVLOGIN @RMTSRVNAME=N'SQL_REMOTE',@USESELF=N'FALSE',@LOCALLOGIN=NULL,@RMTUSER=N'XX',@RMTPASSWORD='XXX'

---EXECUTING SELECT QUERY

SELECT * FROM OPENQUERY(SQL_REMOTE,'SELECT ID,NAME FROM DB_NAME.DBO.TEST')

-----EXECUTING INSERT PASS-THROUGH QUERY

INSERT OPENQUERY(SQL_REMOTE,'SELECT ID,NAME FROM DB_NAME.DBO.TEST')

VALUES(1,'BHASKER')

-----EXECUTING UPDATE PASS-THROUGH QUERY

UPDATE OPENQUERY(SQL_REMOTE,'SELECT NAME FROM DB_NAME.DBO.TEST WHERE ID=1')

SET NAME='BHASKER_NEW'

-----EXECUTING DELTE PASS-THROUGH QUERY

DELETE OPENQUERY(SQL_REMOTE,'SELECT NAME FROM TGI_DBSNP.DBO.TEST WHERE ID=1')

---SET NAME='BHASKER_NEW'