Nisarg Upadhyay
Data from Remote PostgreSQL server

Understanding the OPENQUERY function in SQL Server

June 24, 2022 by

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:

  1. 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.
  2. We cannot use parameters in the OPENQUERY function, and you cannot use them to execute the extended sored procedure on the linked server.
  3. 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.
  4. 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:

The OPENQUERY function is used after the FROM clause. In the syntax,

  1. LinkedServer: Specify the name of the linked server that you want to execute the query.
  2. 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.

Query output:

View linked Server

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:

Output:

Data from Remote SQL Server

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:

Output:

Data from Remote PostgreSQL server

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:

The definition of the tblUsers table is as follows:

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.

Output:

Data inserted in from PostgreSQL to local table

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.

Output

Data inserted in from remote SQL Server  to local table

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:

tblStudent: This table is created in a remote SQL Server database. The definition to create a table is following:

Now, first, let us insert data in the tblStudent table. To do that, run the following query.

Query output

Insert record in tblStudent table

Now, run the following query to change the name of the student.

Query output:

Update record in tblStudent table

Now, run the following query to delete the record from the table.

Query output

Delete record in tblStudent table

Now, let us run the same queries on the PostgreSQL database using OPENQUERY. To insert data in the tblSchool table, run the following query.

Query output:

Insert record in tblSchool table

Now, run the following query to change the name of the school.

Query output:

Update record in tblSchool table

Now, run the following query to delete the record of the school from the tblSchool table.

Query output:

Delete record in tblSchool table

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.

Nisarg Upadhyay
Development, PostgreSQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. 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

3,559 Views