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.
