Copy Data SQL Server dengan bcp

Kepanjangan dari BCP sendiri adalah Bulk Copy Program. Untuk export dan/atau import data di SQL Server bisa menggunakan tool bcp

Untuk contoh kita akan menggunakan database, khususnya tabel Votes. Untuk melihat user dan type authentikasi yang dimiliki masing-masing user gunakan query

SELECT name, type_desc
FROM sys.server_principals
WHERE type_desc IN ('WINDOWS_LOGIN', 'SQL_LOGIN');

WINSERVER2019: Server Name atau hostname atau IP Server
SQLSERVER2019: Nama instance sql server
1433: default port SQL Server

daftar lengkap command bcp

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] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values][-G Azure Active Directory Authentication]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]

Tampilan hasil export dengan format native

dengan custom format

Untuk hasil export, selain native, kita bisa menggunakan pemisah khusus. Contoh kita akan menggunakan tanda pipe “|” sebagai pemisah antar kolom dan dan “\r\n” (CRLF) untuk pemisah baris

bcp StackOverflow2010.dbo.Votes out votes.bcp -S "WINSERVER2019\SQLSERVER2019,1433" -T -t "|" -r "\r\n" -c

format native memiliki ukuran file yang lebih kecil, pada contoh disini 2x lebih kecil dibanding menggunakan custom separator jadi proses export dan import jauh lebih cepat. Waktu export dan import harus menggunakan format yang sama, bila tidak maka proses import akan gagal.

Export dengan bcp

Contoh penggunaan export data SQL Server dengan bcp

Export dengan Windows Authentication

user Windows Authentication adalah user login Windows, biasanya di integrasikan dengan Active Directory.

bcp StackOverflow2010.dbo.Votes out votes.bcp -S "WINSERVER2019\SQLSERVER2019,1433" -T -n

Export dengan SQL Server Authentication

SQL Server Authentication, adalah user native dari SQL Server

bcp StackOverflow2010.dbo.Votes out votes.bcp-S "WINSERVER2019\SQLSERVER2019,1433" -U "USERNAME" -P "PASSWORD" -n

Contoh output perintah bcp

Export dengan Custom Query

Contoh kita akan mengambil semua ID diatas 33000000, format BCP berubah menjadi

bcp "SELECT * FROM StackOverflow2010.dbo.Votes WHERE Id > 33000000" queryout filter_votes.bcp -T -n

Import dengan bcp

Contoh import data SQL Server dengan bcp

Sebelum melakukan import, struktur table harus sudah dibuat terlebih dahulu. Error yang muncul bila belum ada schema table

SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'so_db1.dbo.Votes2'.
SQLState = 37000, NativeError = 11529

Import dengan Windows Authentication

bcp so_db1.dbo.Votes in "C:\Users\ts\Votes.bcp" -S "WINSERVER2019\SQLSERVER2019,1433" -T -n

Export dengan SQL Server Authentication

bcp so_db1.dbo.Votes in "C:\Users\ts\Votes.bcp" -S "WINSERVER2019\SQLSERVER2019,1433" -U "USERNAME" -P "PASSWORD" -n