SQL SERVER 2017 PART-13: OPEN QUERY

What is open query?
Open query executes pass-through query on the specified linked server. This server is an ole db data source.
Syntax:
openquery ( linked_server ,'query' ) .

Arguments.
‘linked_server’
Is an identifier representing the name of the linked server.

‘query’
is the query string executed in the linked server. The maximum length of the string is 8 kb.

Now we will try to understand open query with some examples.
login to sql server management studio. Open a query editor.
I have previously configured a linked server, name oracle db and created customers table.
this select openquery will retrieve all data from customers table.
let’s execute it.

let’s create an insert open-query to append customer id, customer name and city into the table.
Execute it.
Run the select query again to view new row.

Now create an update query to change city name of customer devid to singapore. customer id of devid is 4.

Finally delete using open query.
We are going to delete mr. devid from customers table.
here is the delete statement.
let’s execute it.
result is 1 row affected. that means it is deleted.
run select statement to check it.
excellent; devid is not in result set. he has been deleted.
that’s all for today. please subscribe to my channel to stay connected.