sql sever跨数据库复制数据的方法

详细的用法已经注释在sql代码中了。这个是在sqlserver到sqlserver之间的倒数据。2005，2008，2012应该都是适用的。

--从远程服务器192.168.66.154上查询100条数据，然后导入到dbo.dquestiondata中
insert into dbo.dquestiondata
select top 100 * from
opendatasource('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' ,1
reconfigure
--下面这步可以省略，不带参数的sp_configure，表示查看所有可用的选项
--记住上面一定要加reconfigure语句，目的是使选项立即生效，不然我们
--还是看不到'Ad Hoc Distributed Queries'高级选项
exec sp_configure
--打开'Ad Hoc Distributed Queries'选项
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--记得用完选项后，关闭这些选项
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
--查看一下是不是关闭了
exec sp_configure

 2，用openrowset系统函数

--1,Microsoft OLE DB Provider for SQL Server
--注意第二部分链接字符串的写法很是奇怪Server，user，passwrod是用“；”连接的。
select top 100 * from
openrowset('sqloledb','192.168.6.154';'sa';'xxxxx',Answer.dbo.DQuestionData) 

--2,ODBC数据源的方式：Microsoft OLE DB Provider for ODBC Drivers
select  top 100 * from
openrowset('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.test
select 1,'zhang' union all
select 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返回的是记录集。

 

方法二：

1、新建一个连接服务器，连接到你要导入的服务器的IP地址(或者机器名)

2、点击安全性，使用此安全上下文建立连接，输入数据库服务器的用户名和密码

3、选择要导出的数据库，使用如下sql导数据：

select * into   laobao from [10.180.116.121].ynpdeicp.dbo.LaoBao

Sql解释：

select 要导入的字段 into   要导入的数据表 from [IP地址].数据库名.dbo.数据表名

 

方法三：

使用Sql 工具自带的导入导出功能。

 

方法四：

sql server 数据库导出表里所有数据成insert 语句

 

    有时候，我们想把数据库的某张表里的所有数据导入到另外一个数据库或另外一台计算机上的数据库，对于sql server有这样的一种方法

下面我以sql server 2008 R2,数据库是Northwind数据库为例，

目标：把Northwind数据库的Orders表导出成insert语句。

 

第一步：选择Northwind数据库，右键-任务-生成脚本：

 

第二步：在弹出的“生成和发布脚本”的简介窗口，按“下一步”按钮：

 

第三步：在“选择对象”窗口，选中“选择特定数据库对象”，展开表，

勾选要生成insert语句的表，我这里选的是order表，

按“下一步”按钮：

 

第四步：在弹出的“设置脚本编写选项”窗口，按“高级”按钮，

在弹出的“高级脚本编写选项”中下拉下拉条至底部，

设置“要编写脚本的数据类型”为“仅限数据”(“仅限数据”是只导出数据为insert语句，如果是导出表结构的话选择“仅限架构”，选择“架构和数据”则架构和insert语句都生成)，按“确定”按钮：

 

第五步：在“设置脚本编写选项”窗口，

“指定如何保存或发布脚本”的“输出类型”选中“将脚本保存到特定位置”,

勾选“保存到文件”，则可以指定一个保存的路径，保存为.sql文件，

勾选“保存到新建查询窗口”，则会新打开一个查询窗口，把所有insert 语句放到新查询窗口：

 

第六步：在“设置脚本编写选项”窗口，按“下一步”按钮，弹出：

第七步：在“摘要”窗口，按“下一步”按钮：

第八步：在“保存或发布脚本”窗口，按“完成”按钮：

最终会自动新建一个查询窗口，order表里的所有数据都转换成insert语句。