Executing DML on remote database server using Linked Server/Openrowset

Hello friends, in my last article we saw how we can fetch data from remote database server using Openrowset() / Linked Server, today we are going to learn how we can execute DML (Insert, Update, Delete) statements on remote database server using Openrowset() / Linked Server.

Delete using Openrowset():- To delete rows from a table which is residing on a remote database server, we can use below syntax to perform this task.

delete  from Openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote','select * from emp where eid=106')

 

The above SQL statement will delete the rows from “Emp” table residing on remote database server those will be returned by Openrowset()function.We have only one employee which is having eid=106, hence one row will be deleted.

We can see in the above screenshot, 1 row affected message is being shown in the result window.

 

Update using Openrowset():- To update rows in a table residing on a remote database server, we can use below syntax to perform this task.

update openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote',

'select * from emp where eid=107')

set name='Rohit Sinha'

The above query will update the ”Name” column of the “Emp” table where eid=107.

Insert using Openrowset():- To insert rows in a table residing on a remote database server, we can use below syntax to perform this task.

 

insert openrowset('sqlncli','server=neeraj-pc\server2008;uid=sa;pwd=Mind1234;database=demo_remote',

'select * from emp where eid=107')

values(115,'priyanka arora')

 

The above SQL statement will insert one row into “Emp” table, It means, we just need to include table name in the select query in which we want to insert the rows.

 

DML support in linked Server:- We saw above, how we can use Openrowset() function to execute DML(Insert, Update, Delete), in the same manner we can use linked server to execute DML statements on remote database server. Below is the syntax for the same.

 

Delete using linked Server:-

delete from openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=115')

 

Update using linked Server:-

update openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=107')

set name='Akash Rajput'

 

Insert using Linked Server:-

insert openquery([neeraj-pc\server2008],'select * from demo_remote.dbo.emp where eid=107')

values(115,'Mohan Sharma')

 

This is all about linked server and Openrowset() function, I hope you will find this article useful for you. Please feel free to ask any questions related to this article.