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

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