首页 > 代码库 > sql sever跨数据库复制数据的方法【转】
sql sever跨数据库复制数据的方法【转】
1,用Opendatasource系统函数
详细的用法已经注释在sql代码中了。这个是在sqlserver到sqlserver之间的倒数据。2005,2008,2012应该都是适用的。
--从远程服务器192.168.66.154上查询100条数据,然后导入到dbo.dquestiondata中insert into dbo.dquestiondataselect top 100 * fromopendatasource(‘sqloledb‘,‘data source=192.168.6.154;user id=sa;password=xxxxxx‘).Answer.dbo.DQuestionData--opendatasource 是一个系统函数,第一个参数是Provider Name,第二个参数是Oledb链接字符串,--注意连接字符串里没有指定数据库;数据库名称,Schema,表名在opendatasource函数后面书写。--执行上面的语句会报如下的错,原因是没有开启远程查询支持 --SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource‘ of component ‘Ad Hoc Distributed Queries‘ --because this component is turned off as part of the security configuration for this server. --A system administrator can enable the use of ‘Ad Hoc Distributed Queries‘ by using sp_configure. --For more information about enabling ‘Ad Hoc Distributed Queries‘, see "Surface Area Configuration" in SQL Server Books Online.--解决办法1:在图形界面中,SQL Server 安全配置-->机能的安全配置-->开启远程查询Openrowset和opendatasource的支持--解决办法2:用sp_confing系统存储过程,以命令行的方式开启--首先必须开启‘show advanced options’高级选项,因为‘Ad Hoc Distributed Queries’属于高级选项exec sp_configure ‘show advanced options‘ ,1reconfigure--下面这步可以省略,不带参数的sp_configure,表示查看所有可用的选项--记住上面一定要加reconfigure语句,目的是使选项立即生效,不然我们--还是看不到‘Ad Hoc Distributed Queries‘高级选项exec sp_configure--打开‘Ad Hoc Distributed Queries‘选项exec sp_configure ‘Ad Hoc Distributed Queries‘,1reconfigure--记得用完选项后,关闭这些选项exec sp_configure ‘Ad Hoc Distributed Queries‘,0reconfigureexec sp_configure ‘show advanced options‘,0reconfigure--查看一下是不是关闭了exec sp_configure
2,用openrowset系统函数
--1,Microsoft OLE DB Provider for SQL Server--注意第二部分链接字符串的写法很是奇怪Server,user,passwrod是用“;”连接的。select top 100 * fromopenrowset(‘sqloledb‘,‘192.168.6.154‘;‘sa‘;‘xxxxx‘,Answer.dbo.DQuestionData) --2,ODBC数据源的方式:Microsoft OLE DB Provider for ODBC Driversselect top 100 * fromopenrowset(‘MSDASQL‘,‘DRIVER={SQL Server};SERVER=192.168.6.154;UID=sa;PWD=xxxxx‘,Answer.dbo.DQuestionData)
上面的两种方法都会用到oledb提供商的名称,下面的这个系统存储过程可以查看oledb提供程序的名称
--用于查看oledb提供者名称EXEC master..xp_enum_oledb_providers
3,用链接服务器
如果要多次用到远程查询,每次都写那么长的链接字符串有点麻烦。可以考虑重复利用。
用链接服务器可以很好的解决这个问题
-- 创建链接服务器exec sp_addlinkedserver ‘svr_link‘,‘‘,‘sqloledb‘,‘192.168.6.154‘-- 创建登录信息exec sp_addlinkedsrvlogin ‘svr_link‘,‘false‘,null,‘sa‘,‘xxxx‘--查询 格式为:链接服务器.数据库名.架构名.表名select top 100 * from svr_link.Answer.dbo.DQuestionData-- 删除链接服务器exec sp_dropserver ‘svr_link‘,‘droplogins‘
需要注意的几点:
1,虽然上面的例子都是从远程服务器select数据到本地,
但我们同样可以将本地的数据导向远程服务器
-- 往远程数据库192.168.6.154的Answer数据库的test表插入两条数据insert into svr_link.Answer.dbo.testselect 1,‘zhang‘ union allselect 2,‘wang‘
2,有了链接服务器,就可以用openquery系统函数了
--检索数据select * from openquery(svr_link,‘select * from Answer.dbo.test‘)-- 插入数据insert into openquery(svr_link,‘select * from Answer.dbo.test‘)select 3,‘li‘
3,opendatasource和链接服务器都是只返回Server部分,要查询表需要进一步指定数据库,架构和表名。而openrowset和openquery返回的是记录集
sql sever跨数据库复制数据的方法【转】
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。