【SQL Server数据迁移】链接服务器、分布式查询：SQL Server、ORACLE、Excel、txt、Access

链接服务器为SQL Server提供了从远程数据源访问数据的能力，可以执行查询、修改数据、远程过程调用。远程数据源可以是同类的(数据源可以是另一个SQL Server实例)，也可以是不同类的(其他关系型数据库产品和数据源，如：DB2，ORACLE，EXCEL，ACCESS，文本文件)。

使用跨平台联接查询可以访问遗留数据库，而且不需要对既有的数据源进行合并、迁移。

 

分布式查询可以引用一个或多个链接服务器进行查询，在远程表、视图、存储过程上执行读取和修改操作。链接服务器支持的查询类型取决于OLE DB访问接口提供的事务支持级别。

可以通过在from子句中使用4部分组成的远程对象名称来引用链接服务器，或者使用T-SQL命令openquery来运行分布式查询。

本文主要讲创建到5种数据源的链接服务器，还有bcp的方式来导入导出数据，还有openrowset的特殊用法：

1、SQL Server

2、ORACLE

3、Excel

4、txt

5、Access

6、bcp方式导入导出数据

7、openrowset来执行存储过程

1、SQL Server

--修改高级参数sp_configure 'show advanced options',1go--允许即席分布式查询sp_configure 'Ad Hoc Distributed Queries',1go--如果配置的值不在合理范围（在最小值最大值范围内），那么可以强制覆盖reconfigure with override  go/*=============================================================在分布式即席查询中，select into语句不能用于把本地的表导出到远程服务器解决办法：要在远程服务器新建表，必须通过execute at来执行DDL语句，而要执行execute at，必须先设置链接服务器的rpc、rpc out属性为true，最后用insert into把本地的数据导入到远程===============================================================*/--1、链接服务器为SQL Server--1.1.1建立链接服务器,通过@datasrc指定数据源，适合链接其他多种数据库EXEC master.dbo.sp_addlinkedserver @server = N'Link',                                   @srvproduct='ms',                                    @provider=N'SQLNCLI',                                                                       @datasrc=N'192.168.1.5,1433'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Link',                                    @useself=N'False',                                    @locallogin=NULL,                                    @rmtuser=N'sa',                                    @rmtpassword='xyz'--1.1.2建立链接服务器,通过@server参数指定服务器，适合直接连接sql serverEXEC master.dbo.sp_addlinkedserver @server = N'192.168.1.5,1433',                                    @srvproduct=N'SQL Server'EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'192.168.1.5,1433',                                     @useself=N'False',                                     @locallogin=NULL,                                     @rmtuser=N'sa',                                     @rmtpassword='xyz'--1.1.3设置链接服务器的属性，从而远程调用存储过程rpcEXEC master.dbo.sp_serveroption @server=N'192.168.1.5,1433',                                 @optname=N'rpc out',                                 @optvalue=N'true'EXEC master.dbo.sp_serveroption @server=N'192.168.1.5,1433',                                 @optname=N'rpc',                                 @optvalue=N'true'--1.1.4.1通过链接服务器来执行分布式查询select * from link.wc.dbo.wccselect * from [192.168.1.5,1433].wc.dbo.wcc--1.1.4.2通过openquery来执行分布式查询,需要先建立链接服务器select *from openquery([link],'select * from wc.dbo.wcc')--1.1.5查看链接服务器select server_id,       name,       product,       provider,       data_source,              is_linked,       is_remote_login_enabled,       is_remote_proc_transaction_promotion_enabled,       lazy_schema_validation       from sys.servers--查看链接登录名select s.name,       ll.remote_name,       sp.namefrom sys.linked_logins llinner join sys.servers s        on s.server_id = ll.server_id        left join sys.server_principals sp       on sp.principal_id = ll.local_principal_id--1.1.6删除链接服务器登录名映射、删除链接服务器exec sp_droplinkedsrvlogin @rmtsrvname = '192.168.1.5,1433',                           @locallogin = nullexec sp_dropserver @server = '192.168.1.5,1433'exec sp_droplinkedsrvlogin @rmtsrvname = 'link',                           @locallogin = nullexec sp_dropserver @server = 'link'--1.2用openrowset函数--1.2.1第一种写法SELECT * FROM OPENROWSET('SQLOLEDB',            'server=192.168.1.5,1433;uid=sa;pwd=xyz', --字符串           wc.dbo.wcc)                           --直接写表的名称  --1.2.2第二种写法SELECT * FROM OPENROWSET('SQLOLEDB',            'server=192.168.1.5,1433;            uid=sa;            pwd=xyz',                     --字符串           'select * from wc.dbo.wcc             where wcc_name =''abc''')          --查询语句,注意不能用字符串连接符--1.3用opendatasource函数select *from opendatasource('SQLOLEDB',               'DATA SOURCE=192.168.1.5,1433;                USER ID=sa;                password=xyz').wc.dbo.wcc

2、Oracle

--================================================================--2、链接服务器为Oracle,使用oracle的oledb库MSDAORA--2.1.1为建立链接服务器EXEC sp_addlinkedserver @server = 'OracleLinkedServer',    --要创建的链接服务器名称                @srvproduct = 'Oracle',            --产品名称@provider = 'MSDAORA',             --OLE DB 字符  @datasrc = 'orcl'                  --数据源  --2.1.2为创建连接服务器登陆EXEC sp_addlinkedsrvlogin @rmtsrvname = 'OracleLinkedServer',  --链接服务器名称@useself = 'false', @locallogin = NULL,@rmtuser = 'scott',                  --Oracle服务器的登陆用户名 @rmtpassword = 'tiger'               --Oracle服务器的登陆密码 --2.2为设置链接服务器属性exec sp_serveroption @server='OracleLinkedServer',                     @optname = 'rpc',      --从给定的服务器启动rpc                     @optvalue ='true'      --这样可以调用远程的存储过程exec sp_serveroption @server='OracleLinkedServer',                     @optname = 'rpc out',  --对给定的服务器启动rpc                      @optvalue ='true'      --在远程执行DDL语句--2.3为在远程服务器上执行DDL语句新建表，用oracle的方式书写语句exec('create table SCOTT.EMPXX(iddd int)') at OracleLinkedServer--2.4用openrowset函数，把oracle的数据直接新建表的同时插入数据select * INTO EMP_X from openrowset('MSDAORA','orcl';'scott';'tiger',SCOTT.EMP)select * INTO EMP_X from openrowset('MSDAORA','orcl';'scott';'tiger','select * from SCOTT.EMP')--2.5为opendatasource函数，查询一下oracle中的数据select * from opendatasource('MSDAORA','Data Source=orcl;                          User ID=scott;                          password=tiger')..SCOTT.EMP

3、Excel

/*==================================================================1.很难从显示的错来知道：openrowset,opendatasource这两个函数哪儿出错了。2.必须保证函数中的参数正确，否则会有莫名其妙的错误。3.由于导入的Excel文件格式只能是.xls，所以在导入数据时， 可以先把要导入的扩展名为.xlsx的文件另存为扩展名.xls的文件， 然后把原始.xlsx数据，在格式不变的情况下复制到新的.xls。 4.在导入数据时，必须要按照目标表的字段类型、长度，来转化excel中的数据， 比如，那么在导入时把float转化成numeric，然后再转成varchar， 要是直接从float转成varchar，会把有些数转化成用科学计数法显示的数字， 再进一步转化是会报错。 5.导入到远程的目标表时，目标表必须已经存在  ========================================================================*/--3.Excel--3.1.1链接服务器excel,不用建立服务器登录名EXEC sp_addlinkedserver      @server = 'EXCEL',     @srvproduct = 'Jet 4.0',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc  = 'c:\t.xls',      --@location =  NULL,     @provstr  = 'Excel 5.0;'  --不加分号也可以   --3.1.2通过链接服务器查询SELECT *FROM [EXCEL]...[sheet1$]--3.1.3引用12.0库EXEC sp_addlinkedserver      @server = 'EXCEL',     @srvproduct = 'Jet 12.0',     @provider = 'microsoft.ace.oledb.12.0',     @datasrc  = 'c:\t.xls',      --@location =  NULL,     @provstr  = 'Excel 12.0;'  --不加分号也可以 SELECT *FROM [EXCEL]...[sheet1$]   -    -查询语句完全一样--3.2.1用openrowset函数,引用4.0的库select *from openrowset('microsoft.jet.oledb.4.0',           'Excel 5.0;database=c:\t2.xls',  --文件必须存在            sheet1$)                        --表必须存在            select *fromopenrowset('microsoft.jet.oledb.4.0',           'Excel 5.0;database=c:\t2.xls',           'select * from [sheet1$]')/*=========================================================================如果在要导入数据的目标机器上装有office2007及其以上版本，那么可以用以下的方法导入扩展名为.xlsx,引用了库：microsoft.ACE.oledb.12.0,excel的版本为:12.0注意：不能在openrowset中写任何的空格等多余的字符，      否则会报错，而且错误没有针对性===========================================================================*/--3.2.2用openrowset函数,引用4.0的库select *from openrowset('microsoft.ace.oledb.12.0',                'Excel 12.0;database=c:\test.xlsx',                'select * from [sheet1$]')--3.3.1用opendatasrouce,引用4.0的库select *from opendatasource('microsoft.jet.oledb.4.0',               'data source=c:\t.xls;Extended Properties=Excel 5.0')...[sheet1$]               --3.3.2用opendatasrouce,引用12.0的库select *from opendatasource('microsoft.ace.oledb.12.0',               'data source=c:\t.xls;Extended Properties=Excel 12.0')...[sheet1$]   

4、txt

                                                  --===========================================================--5.txt,cvs格式的数据导入--5.1建立txt的连接服务器EXEC sp_addlinkedserver      @server = 'txtsrv',           @srvproduct  = 'Jet 4.0',      @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc  = 'c:\',                    --文件所在的目录     --NULL,     @provstr = 'Text'--设置登录名EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULLGO--列出在链接服务器上的表,会列出目录中所有的文件:t#txt , list#csvEXEC sp_tables_ex txtsrvGO--查询SELECT * FROM txtsrv...[t#txt]--5.2.1通过bulk insert的方式导入数据,必须先建立一个表create table tt(a1 varchar(10),                a2 varchar(10),                a3 varchar(10))--5.2.2大量插入,表的字段必须与要导入的文件中的字段数量一致BULK INSERT ttFROM 'c:\t.txt'         --扩展名为.csv的文件也可以WITH(FIELDTERMINATOR = ',',  --字段分隔符ROWTERMINATOR = '\n')   --行终止符--5.2.3SELECT * FROM tt/*====================================================5.3用逗号分隔字段，导入txt和csv格式的文件在32位系统上通过openrowset函数，用Microsoft Text Driver或OLE DB Provider for Jet库将txt和CSV数据导入SQL Server表，但是没有大容量导入优化。======================================================*/--5.3.1csv格式，通过Microsoft Text Driver驱动select * from openrowset('MSDASQL',            'Driver={Microsoft Text Driver (*.txt; *.csv)};            DBQ=C:\;',                 --驱动程序,存放txt或cvs的文件路径                                                                                             'select * from list.csv')  --用文件名称做为表名--5.3.2txt格式，通过Microsoft Text Driver驱动select * from openrowset('MSDASQL',            'Driver={Microsoft Text Driver (*.txt; *.csv)};            DBQ=C:\;',                 --驱动程序,存放txt或cvs的文件路径                                                                                             'select * from list.txt')/*===================================================如果分隔符不是逗号那么需要定义schema.ini文件。schema.ini要和数据文件放在同一个目录下面.schema.ini的定义方法：[t.txt]COLNAMEHEADER=TRUEFORMAT=csvdelimitedcol1=name1 shortcol2=name2 shortcol3=name3 shortcol4=name4 short  第一行:如果文本文件的数据有列名，并且作为查询结果的列名的话，     则ColNameHeader应该为true，否则定义为false，     但是如果后面定义了列名的话，则不使用文本文件中数据的列名，而使用后面定义的列名。     但是如果数据有列名的，最好定义为true，否则的话，数据的列名将作为表中的数据。         第二行:format定义数据的分隔符。     一般的分隔符：空格，制表符，逗号。     分别对应：delimited( )(注意括号里应该有一个空格),tabdelimited,csvdelimited。     在选择分隔符时，建议使用tab，因为数据看起来比较整齐，但不能为保证数据的严格整齐，     而在数据当中使用多个tab，这样会导致读取数据时出现错误。其他的逗号和空格同样。         第三行到最后:指定每一列的列名、数据类型、字符集(使用ansi或者oem)、数据类型转换。                       jet数据类型：bit,byte,short,long,currency,                      single,double,datetime,text,momo       ODBC数据类型：char,float,int,longchar,date.                     后面两个如果没有特殊情况的话可以不写，默认即可。==========================================================================*/  --5.3.3txt格式，通过OLE DB Provider for Jetselect * from openrowset('microsoft.jet.oledb.4.0',           'text;            hdr=yes;            database=c:\',    --文件的路径                        t#txt)            --格式为：文件名称#扩展名--5.3.4csv格式，通过OLE DB Provider for Jetselect * from openrowset('microsoft.jet.oledb.4.0',           'text;            hdr=yes;            database=c:\',    --文件的路径                        list#csv)            --格式为：文件名称#扩展名

5、Access

--6.Access--6.1建立连接服务器exec sp_addlinkedserver     @server ='ACCESSsrv',     @srvproduct ='Access',     @provider = 'Microsoft.Jet.OLEDB.4.0',     @datasrc = 'c:\t.mdb'/*=========================================== 1.若要访问不安全的 Access 数据库，尝试访问Access数据库的SQL Server登录名,应将登录映射定义为用户名Admin，且不带密码:  exec sp_addlinkedsrvlogin     @rmtsrvname = 'ACCESSsrv',    @userself   = false,    @locallogin = 'Joe',     @rmtuser = 'Admin',    @rmtpassword = NULL2.若要访问安全的Access数据库，请使用注册表编辑器配置注册表，以使用Access所用的正确的工作组信息文件。使用注册表编辑器,把Access使用工作组信息文件的完整路径名添加到下面注册表项：HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB配置完注册表项后，创建本地登录名到Access登录名的登录名映射：  EXEC sp_addlinkedsrvlogin       'ACCESSsrv',       false,      'Joe',      'AccessUser',   --用户名      '034fhd99dl'    --密码===================================================================*/SELECT * FROM [ACCESSsrv]...wc--6.2使用12.0库导入access数据SELECT *FROM OPENROWSET('microsoft.ace.oledb.12.0', --库名称           'C:\t.accdb';'admin';'',    --'路径和文件名称';'用户名';'密码'           e);                         --表名--6.3使用4.0库导入access数据SELECT *FROM OPENROWSET('microsoft.jet.oledb.4.0',  --库名称           'C:\t.mdb';'admin';'',      --'路径和文件名称';'用户名';'密码'           'select * from wc');        --表名

 6、bcp

--4.通过bcp导入导出数据,通过openrowset导入数据/*=========================================================以下为bcp命令行常用的参数,注意大小写：  -c  以char作为存储类型-w  和-c类似，只有当使用unicode字符集拷贝数据时使用nchar作为存储类型-T  用可信连接-S  sql server服务器名称in  导入数据out 导出数据queryout 查询导出数据-U  用户名-P  密码-F  从哪一行开始导入-L  直到哪一行结束-f  格式文件-x  与-f一起使用，用来生产xml格式的格式文件-t  指定字段分隔符是"\t"-r  指定行分隔符是"\n"=====================================================*/--导出格式化文件--4.1.1用可信连接登陆bcp wc.dbo.calendar format nul -f c:\calendar.fmt                                -c -T -S pc0227gry\mssqlserver2008--4.1.2用户名、密码登陆bcp wc.dbo.calendar format nul -f c:\calendar.fmt                                -c -Usa -Pxyz -S pc0227gry\mssqlserver2008--4.2导出数据文件，可以把远处服务器的数据导出保存为本地的文件bcp wc.dbo.calendar out c:\calendar.txt  -f c:\calendar.fmt                                          -c -T -S PC0227GRY\MSSQLSERVER2008--4.3导入数据bcp wc.dbo.calendar in c:\calendar.txt -c -f c:\calendar.fmt                                        -Usa -Pxyz -S pc0227gry\mssqlserver2008/*=============================================================4.3openrowset函数带bulk操作符的用法第1种用法：openrowset(bulk 要加载结果集的文件名,     formatfile = 格式化文件名,     bulk选项)第2种用法：openrowset(bulk 文件名,     SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB)==============================================================*/--4.4.1第1种用法,把bcp导出的数据，导入数据库中--具体导入数据时，可以先用bcp把表的格式文件倒出来，然后在把数据导进去，这样按照格式自动转化select *from openrowset(bulk 'c:\calendar.txt',             --要读取的文件路径和名称                 formatfile='c:\calendar.fmt',  --格式化文件的路径和名称                                firstrow = 1,                      --要载入的第一行                --lastrow  = 1000,                 --要载入的最后一行,此值必须大于firstrow                                maxerrors = 10,                         --在加载失败之前加载操作中最大的错误数                errorfile ='c:\calendar_error.txt', --存放错误的文件                                rows_per_batch = 100                    --每个批处理导入的行数          ) as calendar --4.4.2第2种用法 select 1,       BulkColumn   --这个是从数据源获取的，要插入的列，默认的列名from openrowset(                 BULK 'c:\helloworld.class',--指定文件路劲，这里是本地路径                      SINGLE_BLOB   --指定二进制数据是SINGLE_BLOB                                    --指定文本是SINGLE_CLOB、SINGLE_NCLOB               )X   --必须要别名select 1,       binColumn   --引用后面定义的列别名from openrowset(                 BULK 'c:\Katongxiar01.jpg', --指定文件路劲，这里是本地路径                      SINGLE_BLOB         --指定二进制数据是SINGLE_BLOB                                          --指定文本是SINGLE_CLOB、SINGLE_NCLOB               )X(binColumn)  --这里还定义了列的别名--可以这么更新               update tset pic = (     select BulkColumn     from openrowset(bulk 'c:\dwhj.bmp',                          SINGLE_BLOB) AS X  --一定不要忘了定义别名                                                          select 1,       binColumn   --引用后面定义的列别名from openrowset(                 BULK 'c:\calendar.txt', --指定文件路劲，这里是本地路径                      SINGLE_CLOB         --指定二进制数据是SINGLE_BLOB                                          --指定文本是SINGLE_CLOB、SINGLE_NCLOB               )X(binColumn)  --这里还定义了列的别名                    

7、通过openrowset来执行存储过程，但是存储过程不能带参数：

--可以执行存储过程--在调用openrowset等函数，注意参数格式，以及参数之间的字符的格式。SELECT *   FROM OPENROWSET('SQLOLEDB',                'Server=PC0627JVC\MSSQLSERVER2008;Trusted_Connection=yes;database=master',                 'exec sp_lock')                 SELECT *   FROM OPENROWSET('SQLOLEDB',                'Server=PC0627JVC\MSSQLSERVER2008;uid=sa;pwd=xxx;',                 'exec master.dbo.sp_lock')