導航:首頁 > 程序命令 > bcp導入命令

bcp導入命令

發布時間:2025-01-03 05:54:29

① 在cmd中使用bcp 工具導出數據的問題

BCP是SQL Server中負責導入導出數據的一個命令行工具,它是基於DB-Library的,並且能以並行的方式高效地導入導出大批量的數據。BCP可以將資料庫的表或視圖直接導出,也能通過SELECT FROM語句對表或視圖進行過濾後導出。在導入導出數據時,可以使用默認值或是使用一個格式文件將文件中的數據導入到資料庫或將資料庫中的數據導出到文件中。

下面將詳細討論如何利用BCP導入導出數據。

1. BCP的主要參數介紹

BCP共有四個動作可以選擇。

(1) 導入。

這個動作使用in命令完成,後面跟需要導入的文件名。

(2) 導出。

這個動作使用out命令完成,後面跟需要導出的文件名。

(3) 使用SQL語句導出。

這個動作使用queryout命令完成,它跟out類似,只是數據源不是表或視圖名,而是SQL語句。

(4) 導出格式文件。

這個動作使用format命令完成,後而跟格式文件名。

下面介紹一些常用的選項:

-f format_file

format_file表示格式文件名。這個選項依賴於上述的動作,如果使用的是in或out,format_file表示已經存在的格式文件,如果使用的是format則表示是要生成的格式文件。

-x

這個選項要和-f format_file配合使用,以便生成xml格式的格式文件。

-F first_row

指定從被導出表的哪一行導出,或從被導入文件的哪一行導入。

-L last_row

指定被導出表要導到哪一行結束,或從被導入文件導數據時,導到哪一行結束。

-c

使用char類型做為存儲類型,沒有前綴且以"t"做為欄位分割符,以"n"做為行分割符。

-w

和-c類似,只是當使用Unicode字元集拷貝數據時使用,且以nchar做為存儲類型。

-t field_term

指定字元分割符,默認是"t"。

-r row_term

指定行分割符,默認是"n"。

-S server_name[ instance_name]

指定要連接的SQL Server伺服器的實例,如果未指定此選項,BCP連接本機的SQL Server默認實例。如果要連接某台機器上的默認實例,只需要指定機器名即可。

-U login_id

指定連接SQL Sever的用戶名。

-P password

指定連接SQL Server的用戶名密碼。

-T

指定BCP使用信任連接登錄SQL Server。如果未指定-T,必須指定-U和-P。

-k

指定空列使用null值插入,而不是這列的默認值。

2. 如何使用BCP導出數據

(1) 使用BCP導出整個表或視圖。

BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password" --使用密碼連接



BCP AdventureWorks.sales.currency out c:currency1.txt -c -T --使用信任連接

下面是上述命令執行後的輸出結果

Starting ...

105 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total : 10 Average : (10500.00 rows per sec.)

下面是currency1.txt的部分內容

AED Emirati Dirham 1998-06-01 00:00:00.000

AFA Afghani 1998-06-01 00:00:00.000

... ... ...

... ... ...

ZWD Zimbabwe Dollar 1998-06-01 00:00:00.000

在使用密碼登錄時需要將-U後的用戶名和-P後的密碼加上雙引號。

註:BCP除了可以在控制台執行外,還可以通過調用SQL Server的一個系統存儲過程xp_cmdshell以SQL語句的方式運行BCP。如上述第一條命令可改寫為

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency out c:currency1.txt -c -U"sa" -P"password"'
執行xp_cmdshell後,返回信息以表的形式輸出。為了可以方便地在SQL中執行BCP,下面的命令都使用xp_cmdshell執行BCP命令。

(2) 對要導出的表進行過濾。

BCP不僅可以接受表名或視圖名做為參數,也可以接受SQL做為參數。通過SQL語句可以對要導出的表進行過濾,然後導出過濾後的記錄。

EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -c -U"sa" -P"password"' (xp表示本存儲過程是擴展的存儲過程,extendprocere,cmdshell是commandshell的意思,也就是直接敲命令執行。)

BCP還可以通過簡單地設置選項對導出的行進行限制。

EXEC master..xp_cmdshell 'BCP "SELECT TOP 20 * FROM AdventureWorks.sales.currency" queryout c:currency2.txt -F 10 -L 13 -c -U"sa" -P"password"'

這條命令使用了兩個參數-F 10和-L 13,表示從SELECT TOP 20 * FROM AdventureWorks.sales.currency所查出來的結果中取第10條到13條記錄進行導出。

3. 如何使用BCP導出格式文件

BCP不僅可以根據表、視圖導入導出數據,還可以配合格式文件對導入導出數據進行限制。格式文件以純文本文件形式存在,分為一般格式和xml格式。用戶可以手工編寫格式文件,也可以通過BCP命令根據表、視圖自動生成格式文件。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format1.fmt -c -T'

上述命令將currency表的結構生成了一個格式文件currency_format1.fmt,下面是這個格式文件的內容。

9.0

3

1 SQLCHAR 0 6 "t" 1 CurrencyCode SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 100 "t" 2 Name SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 24 "rn" 3 ModifiedDate

這個格式文件記錄了這個表的欄位(共3個欄位)類型、長度、字元和行分割符和欄位名等信息。

BCP還可以通過-x選項生成xml格式的格式文件。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency format nul -f c:currency_format2.fmt -x -c -T'
xml格式文件所描述的內容和普通格式文件所描述的內容完全一樣,只是格式不同。

4. 如何使用BCP導入數據

BCP可以通過in命令將上面所導出的currency1.txt和currency2.txt再重新導入到資料庫中,由於currency有主鍵,因此我們將復制一個和currency的結構完全一樣的表。

SELECT TOP 0 * INTO AdventureWorks.sales.currency1 FROM AdventureWorks.sales.currency
將數據導入到currency1表中

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -T'
導入數據也同樣可以使用-F和-L選項來選擇導入數據的記錄行。

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -c -F 10 -L 13 -T'
在導入數據時可以根據已經存在的格式文件將滿足條件的記錄導入到資料庫中,不滿足則不導入。如上述的格式文件中的第三個欄位的字元長度是24,如果某個文本文件中的相應欄位的長度超過24,則這條記錄將不被導入到資料庫中,其它滿足條件的記錄正常導入。

使用普通的格式文件

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -f c:currency_format1.fmt -T'
使用xml格式的格式文件

EXEC master..xp_cmdshell 'BCP AdventureWorks.sales.currency1 in c:currency1.txt -F 10 -L 13 -c -x -f c:currency_format2.fmt -T'

總結

BCP命令是SQL Server提供的一個快捷的數據導入導出工具。使用它不需要啟動任何圖形管理工具就能以高效的方式導入導出數據。當然,它也可以通過xp_cmdshell在SQL語句中執行,通過這種方式可以將其放到客戶端程序中(如delphi、c#等)運行,這也是使客戶端程序具有數據導入導出功能的方法之一。

xp_cmdshell
以操作系統命令行解釋器的方式執行給定的命令字元串,並以文本行方式返回任何輸出。授予非管理用戶執行 xp_cmdshell 的許可權。

說明 在 Microsoft® Windows® 95 或 Microsoft Windows 98 操作系統中執行 xp_cmdshell 時,將不把 xp_cmdshell 的返回代碼設置為喚醒調用的可執行文件的進程退出代碼。返回代碼始終為 0。

語法
xp_cmdshell {'command_string'} [, no_output]

參數
'command_string'

是在操作系統命令行解釋器上執行的命令字元串。command_string 的數據類型為 varchar(255) 或 nvarchar(4000),沒有默認值。command_string 不能包含一對以上的雙引號。如果由 command_string 引用的文件路徑或程序名稱中有空格,則需要使用一對引號。如果使用嵌入空格不方便,可考慮使用 FAT 8.3 文件名作為解決辦法。

no_output

是可選參數,表示執行給定的 command_string,但不向客戶端返回任何輸出。

返回代碼值
0(成功)或 1(失敗)

結果集
執行下列 xp_cmdshell 語句將返回當前目錄的目錄列表。

xp_cmdshell 'dir *.exe'

行以 nvarchar(255) 列的形式返回。

執行下列 xp_cmdshell 語句將返回隨後的結果集:

xp_cmdshell 'dir *.exe', NO_OUTPUT

下面是結果:

The command(s) completed successfully.

注釋
xp_cmdshell 以同步方式操作。在命令行解釋器命令執行完畢之前,不會返回控制。

當授予用戶執行許可權時,用戶能在 Microsoft Windows NT® 命令行解釋器上執行運行 Microsoft SQL Server™ 的帳戶有權執行的任何操作系統命令。

默認情況下,只有 sysadmin 固定伺服器角色的成員才能執行此擴展存儲過程。但是,也可以授予其他用戶執行此存儲過程的許可權。

當作為 sysadmin 固定伺服器角色成員的用戶喚醒調用 xp_cmdshell 時,將在運行 SQL Server 服務的安全上下文中執行 xp_cmdshell。當用戶不是 sysadmin 組的成員時,xp_cmdshell 將模擬使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帳戶。如果代理帳戶不能用,則 xp_cmdshell 將失敗。這只是針對於 Microsoft® Windows NT® 4.0 和 Windows 2000。在 Windows 9.x 上,沒有模擬,且 xp_cmdshell 始終在啟動 SQL Server 的 Windows 9.x 用戶的安全上下文下執行。

說明 在早期版本中,獲得 xp_cmdshell 執行許可權的用戶在 MSSQLServer 服務的用戶帳戶上下文中運行命令。可以通過配置選項配置 SQL Server,以便對 SQL Server 無 sa 訪問許可權的用戶能夠在 SQLExecutiveCmdExec Windows NT 帳戶的上下文中運行 xp_cmdshell。在 SQL Server 7.0 中,該帳戶稱為 SQLAgentCmdExec。現在,不是 sysadmin 固定伺服器角色成員的用戶將在該帳戶上下文中運行命令,而無需再進行配置更改。

許可權
xp_deletemail 的執行許可權默認授予 sysadmin 固定伺服器角色的成員,但可以授予其他用戶。

重要 如果為 MSSQLServer 服務選用的 Windows NT 帳戶不是本地管理員組的成員,則非 sysadmin 固定伺服器角色成員的用戶將無法執行 xp_cmdshell。

示例
A. 返回可執行文件列表
下例顯示執行目錄命令的 xp_cmdshell 擴展存儲過程。

EXEC master..xp_cmdshell 'dir *.exe'

B. 使用 Windows NT net 命令
下例顯示 xp_cmdshell 在存儲過程中的使用。下例先用 net send 通知用戶 SQL Server 即將關閉,然後用 net pause 暫停伺服器,最後用 net stop 關閉伺服器。

CREATE PROC shutdown10
AS
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down
in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output

C. 不返回輸出
下例使用 xp_cmdshell 執行命令字元串,且不向客戶端返回輸出。

USE master
EXEC xp_cmdshell ' c:\sqlmps\pubs.dmp \\server2\backups\sqlmps',
NO_OUTPUT

D. 使用返回狀態
在下例中,xp_cmdshell 擴展存儲過程也給出了返回狀態。返回代碼值存儲在變數 @result 中。

DECLARE @result int
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'

E. 將變數內容寫入文件
下例將當前目錄內容寫入當前伺服器目錄下名為 dir_out.txt 的文件中。

DECLARE @cmd sysname, @var sysname
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd
例如:
1、寫成存儲過程,建立作業定時備份~~~

--在sql中映射一下就可以了
exec master..xp_cmdshell 'net use z: \\yi\D$ "密碼" /user:yi\administrator'

--備份;with init覆蓋|noinit添加
backup database 庫名 to disk='E:\H_BACKUP.bak' with init

--COPY
exec master..xp_cmdshell ' E:\H_BACKUP.bak z:'

--刪除(這句可以去掉)
--exec master..xp_cmdshell 'del E:\H_BACKUP.bak'

--完成後刪除映射
exec master..xp_cmdshell 'net use z: /delete'
2、操作DOC下的查詢SQL語句,也就是osql的用法
用法: osql [-U 登錄 ID] [-P 密碼]
[-S 伺服器] [-H 主機名] [-E 可信連接]
[-d 使用資料庫名稱] [-l 登錄超時值] [-t 查詢超時值]
[-h 標題] [-s 列分隔符] [-w 列寬]
[-a 數據包大小] [-e 回顯輸入] [-I 允許帶引號的標識符]
[-L 列出伺服器] [-c 命令結束] [-D ODBC DSN 名稱]
[-q "命令行查詢"] [-Q "命令行查詢" 並退出]
[-n 刪除編號方式] [-m 錯誤級別]
[-r 發送到 stderr 的消息] [-V 嚴重級別]
[-i 輸入文件] [-o 輸出文件]
[-p 列印統計信息] [-b 出錯時中止批處理]
[-X[1] 禁用命令,[退出的同時顯示警告]]
[-O 使用舊 ISQL 行為禁用下列項]
<EOF> 批處理
自動調整控制台寬度
寬消息
默認錯誤級別為 -1 和 1
[-? 顯示語法摘要]
exec master..xp_cmdshell 'osql -U sa -P 19850709 -S (Local) -i E:\Study\SQL\Data\SqlText\cmdshellTest.sql'
註:路徑的文件夾名稱中間不能有空格

閱讀全文

與bcp導入命令相關的資料

熱點內容
java網路編程怎麼樣 瀏覽:932
商城分紅返利源碼 瀏覽:732
如何設置加密共享文件 瀏覽:171
安卓手機為什麼會卡頓無法開機 瀏覽:518
伺服器yum配置文件怎麼寫 瀏覽:544
東方財富app上買的基金在哪裡查詢 瀏覽:517
液壓廢料圓型壓縮機 瀏覽:166
梁體加密怎麼算 瀏覽:146
堡壘之夜什麼時候出安卓手游 瀏覽:937
如何屏蔽安卓氣泡 瀏覽:804
花生殼內網ftp伺服器地址 瀏覽:493
單片機籃球計數器 瀏覽:688
網頁伺服器ip地址找不到怎麼辦啊 瀏覽:342
無線網加密模式是什麼 瀏覽:835
河圖洛書pdf 瀏覽:737
php5中文函數手冊 瀏覽:654
伺服器如何防止刷東西 瀏覽:265
命令與征服4等級 瀏覽:725
冰雪看劇程序員那麼可愛 瀏覽:367
唱吧app如何一鍵修復 瀏覽:987