BCP چیست

برای انتقال داده بین منابع خارجی و SQL Server راههای متعددی وجود دارد. برای این کار می توان از Linked Server، ابزار SSIS و یا ابزار Import Data در خود SQL Server استفاده کرد. برای آشنایی بیشتر با SSIS و نحوه نصب آن، می توانید به این لینک مراجعه کنید. همچنین برای دیدن سایر مقالات در مورد SQL Server، می توانید به این لینک مراجعه کنید.

BCP، سرحروف کلمات Bulk Copy Program است. از این  ابزار برای انتقال سریع میزان زیادی داده استفاده می شود. راههای دیگری که برای انتقال حجم زیادی از داده ها وجود دارد؛ Import/Export wizard،OpenRowset و همچنین استفاده از Data Flow Taask در SSIS است.

ابزار BCP در واقع یک فرمان در CMD است. این ابزار هم میتواند با  سرعت مناسب حجم زیادی از داده را به SQL Server انتقال دهد؛ و هم می تواند حجم زیادی از دیتای SQL Server را در قالب فایل خروجی ذخیره کند. ذکز این نکته ضروری است که برای   حالت queryouy نیاز به داشتن دانش TSQL است. برای استفاده از این ابزار نیاز به درایور ODBC و نیز Microsoft Command Utility for SQL Server است. اگرچه هر دو این موارد هنگام نصب SQL Server به صورت خودکار نصب می شوند؛ اما برای نصب جداگانه آنها می توانید به این لینک مراجعه کنید. شکل کلی این دستور و پارامترهای آن به صورت تصویر زیر است.

بررسی پارامترها

در این دستور پارامترهای زیادی وجود دارد. در این بخش به بررسی تعدادی از مهمترین پارامترها می پذیریم. این پارامترها در مورد نام سرور، کد پیج، نام دیتابیس، یوزر و پسورد اتصال، اندازه Batch، و تعیین ورود و یا خروج داده ها است.

نام دیتابیس

برای نام دیتابیسی که جدول یا ویو مورد نظر در آن قرار دارد؛ هم می توان نام دیتابیس را به همراه Schema و نام جدول یا ویو آورد. مثل عبارت زیر:

BCP DatabaseName.SchemaName.TableName

 و هم میتوان مقابل پارامتر -d آن را نوشت.

عبارت "query"

این عبارت زمانی استفاده می شود که میخواهیم به جای یک جدول یا ویو، دیتای یک کویری TSQL را در فایل خروجی ذخیره کنیم.

هنگام استفاده از کویری باید حتما از queryout استفاده شود. به مثال زیر توجه کنید.

BCP “SELECT C1,C2 FROM DatabaseName.SchemaName.TableName WHERE C1=1” queryout

عبارات in,out,queryout

زمانی که بخواهیم دیتایی را از فایلی وارد SQL Server کنیم بعد از نوشتن BCP و نام جدول مقصد دیتا، از in استفاده میکنیم. درصورتی که بخواهیم دیتای یک جدول را در فایل خروجی ذخیره کنیم؛ بعد از نوشتن BCP و نام جدول، از out استفاده میکنیم. درصورتی که بخواهیم دیتای خروجی یک کویری را در فایل خروچی ذخیره کنیم؛ بعد از نوشتن BCP و نوشتن کویری در کوتیشن، از queryout استفاده میکنیم. به دستورات زیر توجه کنید.

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt

BCP DatabaseName.SchemaName.TableName   in    d:\data\InputData.txt

BCP “SELECT C1,C2 FROM DatabaseName.SchemaName.TableName WHERE C1=1” queryout d:\data\OutputData.txt

نوع Authentication (احراز هویت)

برای اتصال به سرور SQL و ورود و یا خروج داده، نیاز است که به SQL Server متصل شد. همانطور که می دانیم؛ دو حالت Window و SQL Server برای احراز هویت وجود دارد. در حالت Window با نام کاربری  ویندوزی شخصی که کامن را اجرا میکند؛ احراز هویت صورت می گیرد. در حالت SQL Server با استفاده از نام کاربری و پسورد، احراز هویت صورت میگیرد. در حالتی که میخواهیم دستور CMD با نام کاربری ویندوزی اجرا شود از -T استفاده می کنیم. اگر بخواهیم از حالت احراز هویت SQL Server استفاده کنیم؛ از -U و -P استفاده میکنیم. در مقابل -U نام کاربری و در مقابل -P رمز عبور را می نویسیم.

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt   -U [username]   -P [password]

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt   -T

نام سرور و دیتابیس

برای مشخص کردن نام سرور می توان بعد از پارمتر -S نام سرور را نوشت. همچنین در صورتی که نام دیتابیس را قبلا مشخص نکرده باشیم؛ می توانیم از -d برای مشخص کردن دیتابیس استفاده کنیم. 

BCP SchemaName.TableName   out   d:\data\OutputData.txt    -S   MySqlServer   -d   MyDatabase    -T

نوع کاراکتر و کدپیج

برای مشخص کردن نوع داده ها، در صورتی که بخواهیم هر ستون نوع متفاوتی داشته باشد باید از -f استفاده کرده و نوع داده هر ستون را در یک فایل XML ذخیره کنیم. درصورتی که بخواهیم همه ستونها را کدپیج به عنوان مثال 65001 که همان utf8 است وارد کنیم از -C استفاده میکنیم.

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt   -T  -C65001

 اندازه هر Batch

 برای تعیین تعداد ردیف داده در هر Batch ورود داده، می توان از -b استفاده کرد. هر Batch به عنوان یک ترنزکشن جداگانه انجام شده و به صورت جدا گانه لاگ می شود. به صورت پیش فروص همه داده ها در یک Batch انجام می شود. اما اگر تعداد ردیف های داده شما زیاد است؛ این کار ممکن است به پر شدن لاگ سرور منجر شود و بهتر است اندازه Batch را تعیین کنید. 

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt   -T  -b 50000

در نهایت سه مثال کامل از انواع in و out و queryout را در زیر مشاده میکنید.

BCP DatabaseName.SchemaName.TableName   out   d:\data\OutputData.txt   -T  -C65001  -b 50000

BCP SchemaName.TableName   in    d:\data\OutputData.txt    -S   MySqlServer   -d   MyDatabase    -T

BCP “SELECT C1,C2 FROM DatabaseName.SchemaName.TableName WHERE C1=1” queryout d:\data\OutputData.txt -U  MyUser-P MyPassword  -C65001 -b 50000

پایان