Use of BCP Utility

The syntax is:
bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
{in | out | queryout | format} data_file
[-m max_errors] [-f format_file] [-x] [-e err_file]
[-F first_row] [-L last_row] [-b batch_size]
[-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
[-q] [-C { ACP | OEM | RAW | code_page } ] [-t field_term]
[-r row_term] [-i input_file] [-o output_file] [-a packet_size]
[-S server_name[\instance_name]] [-U login_id] [-P password]
[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]


for examle:

Export:

run cmd:

bcp "select * from db.dbo.table where ..." queryout "\\..." -Ssever -Uuser -Ppass -c

or we can also use exec master..xp_cmdshell to run it.

if table use "out" option instead of queryout

bcp

Import:

declare @bcp as varchar(200)

set @bcp = 'bcp DBname.dbo.tablename in \\server\filepath\tmpTestFile.txt -c -t : -r # -Sserver -Ulogin -Ppassword -F2

exec master..xp_cmdshell @bcp

or:

exec master.dbo.xp_cmdshell 'bcp AdventureWorks.dbo.test_import in c:\temp\employee.txt -S localhost\SQLExpress -T -c -t ""'

For Example, we can write C# code:
using System;
using System.Diagnostics;


using (Process p = new Process())
{
p.StartInfo.FileName = "bcp";
p.StartInfo.Arguments = "databasename .dbo.tablename in myoutput.txt -Sservername -Usa -Ppass -c -t| -F2";
p.Start();
p.WaitForExit();
}

//Note -t| is delimeter is pipe, -F2 is import from line 2

We can specify the output txt format when use bcp ... out in command line.
When input to db from a txt file, we can write the format file by ourselves:

For example, we have a pipe delimited txt file with txt format like the following.
(file name is C:\mybcp.txt)

ID|Name|Age|Sex
1|Jerry|30|Male
2|Lina|15|Female
3|Jay|88|Male
4|Eric|30|NoRecord
5|Someone|40|NoRecord

We can write the format file (C:\mybcptest.fmt) use txt pad:


9.0
4
1 SQLCHAR 0 100 "|" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "|" 2 DBColumn1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "|" 3 Age SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r\n" 4 SourceID SQL_Latin1_General_CP1_CI_AS


Notice the 9.0 is the bcp version, we can use command bcp -v to check the bcp version.
4 means the columns in txt files

After create the format files, we can impot the txt files to database, use command:

bcp mydb.dbo.MyClassmates in C:\mybcp.txt -SMyServer -USa -Ppass -fC:\mybcptest.fmt