SQL Server 2022 : bcp ユーティリティ2023/01/27

	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              2023-01-27 01:44:56.873
model               2003-04-08 09:13:36.390
msdb                2022-10-08 06:31:57.550
SampleDB            2023-01-27 02:25:02.897
SampleDB2           2023-01-27 02:28:10.280

(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...

3 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 12     Average : (250.0 rows per sec.)

root@dlp:~#

cat test.txt

00001,Ubuntu,Linux,2023-01-28
00002,Debian,Linux,2023-01-27
00003,RedHat,Linux,2023-01-27

# その他オプション

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 or tokenfile]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values][-G Azure Active Directory Authentication]
  [-h "load hints"]         [-d database name]