了解SQL Server中的OPENQUERY函数

在这篇文章中，我们将学习OPENQUERY函数。它用于使用链接服务器在远程数据源上运行一个临时的分布式查询。有各种方法来查询远程数据源。它也被用来执行直通式查询，在链接服务器中配置的数据库的表中运行INSERT、UPDATE和DELETE语句。

重要说明。

1. OPENQUERY函数是访问远程服务器数据的一种临时方法。如果你经常查询远程服务器，那么你应该使用链接服务器，而不是使用它。
2. 我们不能在OPENQUERY函数中使用参数，也不能用它们来执行链接服务器上的扩展存储过程。
3. 当我们使用OPENQUERY来访问远程数据时，SQL Server会将查询发送到远程服务器。像解析查询和生成执行计划这样的操作是在远程服务器上进行的。
4. OPENQUERY比链接服务器快，因为当我们使用链接服务器时，SQL服务器会将查询分成本地和远程查询。本地查询在本地服务器上执行，而远程查询将被发送到远程服务器上。SQL服务器合并结果集，并返回最终的结果集。

OPENQUERY函数的语法如下。

  SELECT * FROM OPENQUERY (linkedServer, ‘Query’)

OPENQUERY函数被用在FROM子句之后。在语法上。

1. LinkedServer：指定你要执行查询的链接服务器的名称。
2. 查询：指定你想在链接服务器上执行的T-SQL查询。查询字符串的最大长度为8KB。

环境设置

为了演示OPENQUERY的用法，我在工作站上安装了SQL Server 2019和PostgreSQL13。我已经创建了两个链接服务器。第一台链接服务器连接到远程SQL Server实例。它被命名为Nisarg-PC\SQL01。

第二个链接服务器名为PostgreSQL30，用于连接到PostgreSQL数据库。我已经使用ODBC驱动程序配置了名为PostgreSQL30的DSN（数据源名称）。链接服务器使用这个DSN来访问UKLandRegistery数据库。要查看链接服务器的列表，请执行以下查询。

use master
  go
  select srvid,srvstatus, srvname,srvproduct,datasource,schemadate,catalog from sys.sysservers

查询输出

现在，让我们看看一些例子

例1:运行选择语句

为了在SQL服务器上运行SELECT语句，我们将使用名为Nisarg-PC\SQL01的链接服务器。下面的查询从StackOverflow2010数据库中填充数据。

查询。

select * from openquery([NISARG-PC\SQL01],'select top 100 AboutMe, Age,CreationDate,DisplayName,Views,WebsiteURL from StackOverflow2010.dbo.Users')

输出。

为了在PostgreSQL中运行SELECT语句，我们将使用名为PostgreSQL30的链接服务器。下面的查询从UKLandRegistery数据库中填充数据。

查询。

select * from openquery([POSTGRESQL30],'select price, transfer_date, property_type,locality,city,district,county from land_registry_price_paid_uk limit 100')

输出。

例2：在本地表中插入数据

在这个例子中，我们将学习如何插入从远程源弹出的数据并将其插入本地表。我已经创建了一个名为openQueryDemo的数据库。在openQueryDemo数据库中，我已经创建了两个表，名为tbl_land_registry_price_paid_uk和tblUsers。tbl_land_registry_price_paid_uk表的定义如下。

 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

tblUsers表的定义如下。

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

现在，我们要将land_registry_price_paid_uk表的数据插入到tbl_land_registry_price_paid_uk中。 要做到这一点，请运行下面的查询。

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

输出。

上面的查询从PostgreSQL数据库中创建的表中填充数据，并将其插入到SQL Server中创建的表中。

同样地，我们要把**[dbo].[Users**]表的数据插入到**[dbo][tblUsers]中。** 要做到这一点，请运行以下查询。

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')

输出

上面的查询从远程SQL Server数据库中创建的表中填充数据，并将其插入到SQL Server中创建的表中。

例3：执行DML语句

在这个例子中，我们将学习如何使用OPENQUERY从远程服务器插入、UPDATE和DELETE数据。我已经创建了两个表。

tblSchool：这个表是在一个远程PostgreSQL数据库中创建的。创建表的定义如下。

 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：这个表是在一个远程SQL Server数据库中创建的。创建表的定义如下。

create table tblStudent 
  (ID int identity(1,1) primary key, 
  student_name varchar(500), 
  grade char(1), 
  school_id int
  )

现在，首先，让我们在tblStudent表中插入数据。要做到这一点，请运行以下查询。

INSERT OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent')  
  VALUES ('Nisarg Upadhyay','A',01);

查询输出

现在，运行下面的查询来改变学生的名字。

UPDATE OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent where ID=01')   
  SET student_name = 'Nisarg Dixitkumar Upadhyay';

查询输出。

现在，运行以下查询，从表中删除该记录。

DELETE OPENQUERY ([NISARG-PC\SQL01], 'SELECT student_name, grade,school_id from StackOverflow2010.dbo.tblstudent where ID=01')

查询输出

现在，让我们使用OPENQUERY在PostgreSQL数据库中运行同样的查询。要在tblSchool表中插入数据，运行以下查询。

INSERT OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool"')  
  VALUES (03,'Karvey School');

查询输出。

现在，运行下面的查询来改变学校的名称。

UPDATE OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool" where "School_ID"=01')   
  SET School_Name = 'Nalanda High School';

查询输出。

现在，运行下面的查询，从tblSchool表中删除该学校的记录。

 DELETE OPENQUERY ([POSTGRESQL30], 'SELECT "School_ID","School_Name" FROM public."tblSchool" where "School_ID"=01')  

查询输出。

总结

在这篇文章中，我们了解了OPENQUERY函数和它的用法。我通过在PostgreSQL和远程SQL Server实例上执行查询来解释这个概念。