bcp 小工具及 openrowset/openquery/opendatasource 用法小结

客官°小女子只卖身不卖艺 2022-06-17 09:47 135阅读 0赞

BCP 小工具用法
    
    bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
        {in | out | queryout | format} data_file
        [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
        [-Ffirst_row] [-Llast_row] [-bbatch_size]
        [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] 
        [-q] [-C { ACP | OEM | RAW | code_page } ] [-tfield_term] 
        [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
        [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
        [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
    
    ---可以参考 微软官方网站 http://msdn.microsoft.com/zh-cn/library/ms162802.aspx
    
    用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
      [-m 最大错误数]             [-f 格式化文件]         [-e 错误文件]
      [-F 首行]                   [-L 末行]             [-b 批大小]
      [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
      [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
      [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
      [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
      [-S 服务器名称]             [-U 用户名]           [-P 密码]
      [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
      [-k 保留空值]               [-E 保留标识值]
      [-h"加载提示"]              [-x 生成xml 格式化文件]
    
    --注意： 上面的 字符 区分大小写。俺吃过这个亏，特别提醒一下。
    
    /*WINDOWS身份
    exec master..xp_cmdshell 'bcp mytest.dbo.test1 out e:\test21.xls -T -c'---导出表中的数据到指定文件中
    go
    
    exec master..xp_cmdshell 'bcp mytest.dbo.test1 in e:\test21.xls -T -c'---将指定文件中的数据导入到表中
    go
    --或者
    bulk insert TESTAB from 'e:\test21.xls'
    ---再或者
    insert into TESTAB select * from openrowset(bulk,'e:\test21.xls')
    go
    exec master..xp_cmdshell 'bcp "select top 2 TextData,NTUserName,LoginName from mytest.dbo.trace_log" queryout  e:\test.xls -T -c' ----导出查询的数据到指定的文件中
    */
    /* ---按照SQLSERVER登录名导出表中的数据
    --导出表的情况 
    EXEC   master..xp_cmdshell  'bcp  mytest.dbo.TESTAB  out  "e:\test11.xls"  -c  -S"zhaowenzhong"  -U"sa"  -P"123456" '
    
    --导出查询的情况 
    EXEC   master..xp_cmdshell   'bcp  "SELECT * FROM  mytest.dbo.TESTAB "  queryout  e:\test21.xls   -c  -S"zhaowenzhong"  -U"sa"  -P"123456" '
    */
    
     
    
    --连接远程/局域网数据(openrowset/openquery/opendatasource) 
    --1、openrowset 
    
    --查询示例 
    select * from openrowset( 'SQLOLEDB ', 'server_name '; 'user_name '; 'pwd ',dbname.dbo.tablename)
    
    --生成local_tabname 
    select * into local_tabname from openrowset( 'SQLOLEDB ', 'server_name '; 'user_name '; 'pwd ',dbname.dbo.tablename)
    
    --把local_tabname导入远程表 
    insert openrowset( 'SQLOLEDB ', 'server_name '; 'user_name '; 'pwd ',dbname.dbo.tablename)
    select * from local_tabname  
    
    --更新local_tabname 
    update b 
    set b.ColumnA=a.ColumnA 
     from openrowset( 'SQLOLEDB ', 'server_name '; 'user_name '; 'pwd ',dbname.dbo.tablename)as a inner join local_tabname b
    on a.column1=b.column1 
    
    --openquery用法需要创建一个连接 
    
    --首先创建一个连接创建链接服务器 
    exec sp_addlinkedserver   'Linked_Server_Name ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
    --查询 
    select * 
    FROM openquery(Linked_Server_Name,  'SELECT *  FROM dbname.dbo.tablename ') 
    go
    --把local_tabname导入远程表 
    insert openquery(Linked_Server_Name,  'SELECT *  FROM dbname.dbo.tablename ') 
    select * from local_tabname 
    go
    --更新local_tabname 
    update b 
    set b.ColumnB=a.ColumnB 
    FROM openquery(Linked_Server_Name,  'SELECT * FROM dbname.dbo.tablename ') as a  
    inner join local_tabname b on a.ColumnA=b.ColumnA 
    go
    --3、opendatasource/openrowset 
    SELECT   * 
    FROM   opendatasource( 'SQLOLEDB ',  'Data Source=ip/ServerName;User ID=LogUser_Name;Password=pwd ' ).test.dbo.roy_ta