In this article, we are going to learn about the OPENQUERY function. It is used to run an ad-hoc distributed query on the remote data source using the linked server. There are various ways to query the remote data source. You can read this article, Querying remote data sources in SQL Server, to learn more about querying the remote data source. It is also used to execute pass-through queries to run INSERT, UPDATE and DELETE statements on the tables of the database configured in the linked server.
Important Notes:
- The OPENQUERY function is an ad-hoc method to access the data of a remote server. If you are querying the remote server frequently, then instead of using it, you should use the linked server.
- We cannot use parameters in the OPENQUERY function, and you cannot use them to execute the extended sored procedure on the linked server.
- When we use the OPENQUERY to access the remote data, the SQL Server sends the query to a remote server. The operations like parsing the query and generating the execution plan are performed on the remote server.
- The OPENQUERY is faster than the linked server because when we use the linked server, the SQL Server breaks the query into local and remote queries. The local queries are executed on the local server, and remote queries will be sent to the remote server. The SQL server combines the result set and returns the final result set.
The syntax of the OPENQUERY function is as follows:
|
1 2 3 |
SELECT * FROM OPENQUERY (linkedServer, ‘Query’) |
The OPENQUERY function is used after the FROM clause. In the syntax,
- LinkedServer: Specify the name of the linked server that you want to execute the query.
- Query: Specify the T-SQL query that you want to execute on the linked server. The maximum length of the query string is 8KB.
Environment Setup
To demonstrate the usage of the OPENQUERY, I have installed SQL Server 2019 and PostgreSQL13 on my workstation. I have created two linked servers. The first linked server connects to the remote SQL Server instance. It is named Nisarg-PC\SQL01. To learn how to create a linked server in SQL Server, you can read How to create and configure a linked server in the SQL Server Management Studio article.
The second linked server named PostgreSQL30 is used to connect to the PostgreSQL database. I have configured the DSN (Data source name) named PostgreSQL30 using the ODBC driver. The linked server uses the DSN to access the UKLandRegistery database. To view the list of linked servers, execute the following query.
|
1 2 3 4 5 |
use master go select srvid,srvstatus, srvname,srvproduct,datasource,schemadate,catalog from sys.sysservers |
Query output:
Now, let us see some example
Example 1: Run Select statement
To run the SELECT statement on the SQL Server, we are going to use the linked server named Nisarg-PC\SQL01. The following query populates the data from the StackOverflow2010 database.
Query:
|
1 2 3 |
select * from openquery([NISARG-PC\SQL01],'select top 100 AboutMe, Age,CreationDate,DisplayName,Views,WebsiteURL from StackOverflow2010.dbo.Users') |
Output:
To run the SELECT statement in PostgreSQL, we are going to use the linked server named PostgreSQL30. The following query populates the data from the UKLandRegistery database.
Query:
|
1 2 3 |
select * from openquery([POSTGRESQL30],'select price, transfer_date, property_type,locality,city,district,county from land_registry_price_paid_uk limit 100') |
Output:
Example 2: Insert data in the local table
In this example, we will learn how to insert the data populated from a remote source and insert it in a local table. I have created a database named openQueryDemo. In the openQueryDemo database, I have created two tables named tbl_land_registry_price_paid_uk and tblUsers. The definition of the tbl_land_registry_price_paid_uk table is the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE land_registry_price_paid_uk ( price integer, transfer_date date, property_type varchar(1), locality varchar(5000) , city varchar(5000) , district varchar(5000) , county varchar(5000) ) Go |
The definition of the tblUsers table is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE [Users] ( [Id] [int] IDENTITY(1,1) NOT NULL, [AboutMe] [nvarchar](max) NULL, [Age] [int] NULL, [CreationDate] [datetime] NOT NULL, [DisplayName] [nvarchar](40) NOT NULL, [Views] [int] NOT NULL, [WebsiteUrl] [nvarchar](200) NULL, ) GO |
Now, we are going to insert the data of the land_registry_price_paid_uk table to tbl_land_registry_price_paid_uk. To do that, run the following query.
|
1 2 3 4 5 |
insert into land_registry_price_paid_uk (price, transfer_date, property_type,locality,city,district,county) select * from openquery([POSTGRESQL30],'select price, transfer_date, property_type,locality,city,district,county from land_registry_price_paid_uk limit 100') Go |
Output:
The above query populates the data from the table, which is created in a PostgreSQL database and inserts it in a table created in a SQL Server.
Similarly, we are going to insert the data of the [dbo].[Users] table to [dbo][tblUsers]. To do that, run the following query.
|
1 2 3 4 |
insert into [Users] (AboutMe, Age,CreationDate,DisplayName,Views,WebsiteURL) select * from openquery([NISARG-PC\SQL01],'select top 100 AboutMe, Age,CreationDate,DisplayName,Views,WebsiteURL from StackOverflow2010.dbo.Users') |
Output
The above query populates the data from the table that is created in a remote SQL Server database and inserts it in a table created in a SQL Server.
Example 3: Execute the DML statements
In this example, we will learn how to INSERT, UPDATE and DELETE the data from the remote server using OPENQUERY. I have created two tables.
tblSchool: This table is created in a remote PostgreSQL database. The definition to create a table is following:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE public."tblSchool" ( "School_ID" integer NOT NULL, "School_Name" character varying(5000) COLLATE pg_catalog."default", CONSTRAINT "tblSchool_pkey" PRIMARY KEY ("School_ID") ) |
tblStudent: This table is created in a remote SQL Server database. The definition to create a table is following:
|
1 2 3 4 5 6 7 8 |
create table tblStudent (ID int identity(1,1) primary key, student_name varchar(500), grade char(1), school_id int ) |
Now, first, let us insert data in the tblStudent table. To do that, run the following query.
|
1 2 3 4 |
INSERT OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent') VALUES ('Nisarg Upadhyay','A',01); |
Query output
Now, run the following query to change the name of the student.
|
1 2 3 4 |
UPDATE OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent where ID=01') SET student_name = 'Nisarg Dixitkumar Upadhyay'; |
Query output:
Now, run the following query to delete the record from the table.
|
1 2 3 |
DELETE OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent where ID=01') |
Query output
Now, let us run the same queries on the PostgreSQL database using OPENQUERY. To insert data in the tblSchool table, run the following query.
|
1 2 3 4 |
INSERT OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool"') VALUES (03,'Karvey School'); |
Query output:
Now, run the following query to change the name of the school.
|
1 2 3 4 |
UPDATE OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool" where "School_ID"=01') SET School_Name = 'Nalanda High School'; |
Query output:
Now, run the following query to delete the record of the school from the tblSchool table.
|
1 2 3 |
DELETE OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool" where "School_ID"=01') |
Query output:
Summary
In this article, we learned about the OPENQUERY function and its usage. I have explained the concept by executing the queries on PostgreSQL and remote SQL Server instances.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022