SQLServer跨数据库服务器查询和跨表更新怎么 SQLServer数据库跨数据库服务器查询和跨表更新实例是这样的：想实现的功能很简单,在我的本地一个表用来保存省的信息:T_Province，在另外一台服务器上也有一个保存省的表province,其中有我本地没有的provience_name_en和provience_id信息。下面学步园小编来讲解下SQLServer跨数据库服务器查询和跨表更新怎么操作?
SQLServer跨数据库服务器查询和跨表更新怎么操作
准备工作
首先我在本地T_Province表中添加了ProvinceNameEn和ProvinceId两个字段.接下来就要想办法为这两个字段填充数据.
跨服务器查询
首先需要解决跨服务器查询的问题.先来看我的最终实现:
–创建链接服务器
execsp_addlinkedserver@server=’SQL2′,@srvproduct=”,@provider=’SQLNCLI’,@datasrc=’192.168.9.123′–登录链接服务器execsp_addlinkedsrvlogin@rmtsrvname=’SQL2′,@useself=’false’,@locallogin=null,@rmtuser=’sa’,@rmtpassword=’123456′
上面使用sp_addlinkedserver和sp_addlinkedsrvlogin与服务器建立了链接,接下来就可以直接查询远程服务器上的数据了:
–创建临时表createtable#t(ProvinceNamenvarchar(50),ProvinceNameEnnvarchar(50),ProvinceIDnvarchar(50))INSERTINTO#t(ProvinceName,ProvinceNameEn,ProvinceID)(SELECTlocalDB.ProvinceName,serverDB.province_name_en,serverDB.province_IDFROMT_ProvinceaslocalDB,SQL2.bdg_web_retail.dbo.provinceasserverDBWHERElocalDB.ProvinceName=serverDB.Province_Name)–跨服务器查询生成的临时表结果SELECT*FROM#t
通过上面的SQL语句,我将两个服务器,两个数据库的两个表做了内联查询,并且将结果保存到了本地的临时表#t中.
跨表更新
接下来希望将#t中的数据更新到T_Province表中.其实跨表更新很简单,但是一开始头脑中这个概念,不知道set子句如何写.下面是最后的成果:
–更新本地的T_Province表数据
UPDATET_ProvinceSETT_Province.ProvinceNameEn=(SELECT#t.ProvinceNameEn),T_Province.ProvinceID=(SELECT#t.ProvinceID)ROMT_Province,#tWHERET_Province.ProvinceName=#t.ProvinceName
需要注意的是我最开始使用了Declare建立表变量的形式创建了@t,但是执行update操作时提示”必须声明标量变量@t”,换成了临时表#t就没有问题。
跨服务器查询相关知识
下面对跨服务器查询用到的知识进行讲解.
创建链接服务器sp_addlinkedserver
创建链接服务器。链接服务器让用户可以对OLEDB数据源进行分布式异类查询。在使用sp_addlinkedserver创建链接服务器后，可对该服务器运行分布式查询。假如链接服务器定义为SQLServer实例，则可执行远程存储过程。
语法
sp_addlinkedserver[@server=]’server'[,[@srvproduct=]’product_name’][,[@provider=]’provider_name’][,[@datasrc=]’data_source’][,[@location=]’location’][,[@provstr=]’provider_string’][,[@catalog=]’catalog’]
参数
[@server=]’server’
要创建的链接服务器的名称。server的数据类型为sysname，没有默认值。
[@srvproduct=]’product_name’
要添加为链接服务器的OLEDB数据源的产品名称。product_name的数据类型为nvarchar(128)，默认值为NULL。假如为SQLServer，则不必指定provider_name、data_source、location、provider_string和catalog。
[@provider=]’provider_name’
与此数据源对应的OLEDB访问接口的唯一编程标识符(PROGID)。对于当前计算机中安装的指定OLEDB访问接口，provider_name必须唯一。provider_name的数据类型为nvarchar(128)，默认值为NULL;但假如忽略provider_name，则使用SQLNCLI。SQLNCLI是SQL本机OLEDB访问接口。OLEDB访问接口应以指定的PROGID在注册表中注册。
[@datasrc=]’data_source’
由OLEDB访问接口解释的数据源的名称。data_source的数据类型为nvarchar(4000)。data_source作为DBPROP_INIT_DATASOURCE属性传递以初始化OLEDB访问接口。
[@location=]’location’
由OLEDB访问接口解释的数据库的位置。location的数据类型为nvarchar(4000)，默认值为NULL。location作为DBPROP_INIT_LOCATION属性传递以初始化OLEDB访问接口。
[@provstr=]’provider_string’
OLEDB访问接口特定的连接字符串，它可标识唯一的数据源。provider_string的数据类型为nvarchar(4000)，默认值为NULL。provstr或传递给IDataInitialize或设置为DBPROP_INIT_PROVIDERSTRING属性以初始化OLEDB访问接口。
在针对SQL本机客户端OLEDB访问接口创建链接服务器后，可将SERVER关键字用作SERVER=servername\instancename来指定实例，以指定特定的SQLServer实例。servername是运行SQLServer的计算机名称，instancename是用户将连接到的特定SQLServer实例的名称。
[@catalog=]’catalog’
与OLEDB访问接口建立连接时所使用的目录。catalog的数据类型为sysname，默认值为NULL。catalog作为DBPROP_INIT_CATALOG属性传递以初始化OLEDB访问接口。在针对SQLServer实例定义链接服务器时，目录指向链接服务器映射到的默认数据库。
SQLServer跨数据库服务器查询和跨表更新怎么操作
登录链接服务器sp_addlinkedsrvlogin
语法
sp_addlinkedsrvlogin[@rmtsrvname=]’rmtsrvname'[,[@useself=]’useself’][,[@locallogin=]’locallogin’][,[@rmtuser=]’rmtuser’][,[@rmtpassword=]’rmtpassword’]
参数
[@rmtsrvname=]’rmtsrvname’
应用登录映射的链接服务器的名称。rmtsrvname的数据类型为sysname，没有默认值。
[@useself=]’useself’
确定用于连接远程服务器的登录名。useself的数据类型为varchar(8)，默认值为TRUE。
值为true时指定登录使用自己的凭据连接rmtsrvname，忽略rmtuser和rmtpassword参数。false指定使用rmtuser和rmtpassword参数连接指定locallogin的rmtsrvname。假如rmtuser和rmtpassword也设置为NULL，则不使用登录名或密码来连接链接服务器。
[@locallogin=]’locallogin’
本地服务器上的登录。locallogin的数据类型为sysname，默认值为NULL。NULL指定此项应用于连接到rmtsrvname的所有本地登录。假如不为NULL，则locallogin可以是SQLServer登录或Windows登录。对于Windows登录来说，必须以直接的方式或通过已被授权访问的Windows组成员身份授予其访问SQLServer的权限。
[@rmtuser=]’rmtuser’
当useself为false时，表示用于连接rmtsrvname的用户名。rmtuser的数据类型为sysname，默认值为NULL。
[@rmtpassword=]’rmtpassword’
与rmtuser关联的密码。rmtpassword的数据类型为sysname，默认值为NULL。
使用链接服务器
服务器名.数据库名.dbo.表名
删除链接服务器sp_dropserver
语法
sp_dropserver[@server=]’server'[,[@droplogins=]{‘droplogins’|NULL}]
参数
[@server=]’server’
要删除的服务器。server的数据类型为sysname，无默认值。server必须存在。
[@droplogins=]’droplogins’|NULL
指示假如指定了droplogins，那么对于server，还必须删除相关的远程服务器和链接服务器登录名。@droplogins的数据类型为char(10)，默认值为NULL。
以上就是关于“SQLServer跨数据库服务器查询和跨表更新怎么操作”的内容，希望对大家有用。更多资讯请关注学步园。学步园，您学习IT技术的优质平台!
操作