SQL Server 2017 : bcp ユーティリティ2017/10/08 |
bcp (bulk copy program) ユーティリティを利用すると、SQL Server のインスタンスからデータのインポートやエクスポートが可能です。
|
|
[1] | インストール済みの [mssql-tools] パッケージに付属の [bcp] コマンドで実行可能です。 |
[root@dlp ~]# sqlcmd -S localhost -U SA -Q 'select name,create_date from sys.databases' Password: name create_date ----------------------------- ----------------------- master 2003-04-08 09:13:36.390 tempdb 2017-10-10 10:09:41.483 model 2003-04-08 09:13:36.390 msdb 2017-08-22 19:39:22.887 SampleDB 2017-10-05 16:22:42.643 TestDB_DailyJob 2017-10-06 14:10:16.500 (6 rows affected) # [SampleDB] の [Sample_Table] のデータを文字データ型のカンマ区切りで [test.txt] に出力 [root@dlp ~]# bcp SampleDB.dbo.Sample_Table out test.txt -c -t, -S localhost -U SA Password: Starting copy... 4 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 Average : (4000.0 rows per sec.)
[root@dlp ~]#
cat test.txt 00001,CentOS,Linux,2017-10-05 00002,RedHat,Linux,2017-10-05 00003,Fedora,Linux,2017-10-05 00004,Ubuntu,Linux,2017-10-05 # その他オプション [root@dlp ~]# bcp -h usage: bcp {dbtable | query} {in | out | queryout | format} datafile [-m maxerrors] [-f formatfile] [-e errfile] [-F firstrow] [-L lastrow] [-b batchsize] [-n native type] [-c character type] [-w wide character type] [-N keep non-text native] [-q quoted identifier] [-t field terminator] [-r row terminator] [-a packetsize] [-K application intent] [-S server name or DSN if -D provided] [-D treat -S as DSN] [-U username] [-P password] [-T trusted connection] [-v version] [-R regional enable] [-k keep null values] [-E keep identity values] [-h "load hints"] [-d database name] |
Sponsored Link |
|