How to use bcp Utility MSSQL Server using Windows Authentication and SQL Server Authentication

The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.

This examples shows how to use BCP command to transfer data from table to File and then from file to table.

Here I have created empty BCP_ Customers table similar structure of Customers stable.

Now let’s see, how to use BCP utility to Export Data from Table to file using Trusted Connection or Windows Authorization.

>BCP TESTDB.dbo.Customers OUT C:\bcp\Customers.txt -T –c

Arguments

in data_file | out data_file | queryout data_file | format nul

Specifies the direction of the bulk copy, as follows:

• in copies from a file into the database table or view.
• out copies from the database table or view to a file. If you specify an existing file, the file is overwritten. When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.
• queryout copies from a query and must be specified only when bulk copying data from a query.
• format creates a format file based on the option specified (-n, -c, -w, or -N) and the table or view delimiters. When bulk copying data, the bcp command can refer to a format file, which saves you from re-entering format information interactively. The formatoption requires the -f option; creating an XML format file, also requires the -x option. For more information, see Create a Format File (SQL Server). You must specify nul as the value (format nul).

-c

Performs the operation using a character data type. This option does not prompt for each field; it uses char as the storage type, without prefixes and with \t (tab character) as the field separator and \r\n (newline character) as the row terminator. -c is not compatible with -w.

For more information, see Use Character Format to Import or Export Data (SQL Server).

-T

Specifies that the bcp utility connects to SQL Server with a trusted connection using integrated security. The security credentials of the network user, login_id, and password are not required. If –T is not specified, you need to specify –U and –P to successfully log in.

You can find the detail arguments detils in the follwing link:
https://msdn.microsoft.com/en-us/library/aa337544.aspx

You can see the output file in the output folder

Now let’s see, how to use BCP utility to Export Data from Table to file using SQLServer Authentication or security credentials.

BCP utility will helps if it finds any appropriate command

>BCP TESTDB.dbo.Customers OUT C:\bcp\Customers1.txt -Slocalhost -Usa -P123 –c

-S: Server Name (localhost)

-U: Username (sa)

-P: Password (123)

Now let’s see, how to use BCP utility to Import Data from File to Table using Server Authentication or security credentials.
>BCP TESTDB.dbo.BCP_Customers IN C:\bcp\Customers.txt -T -c

Now let’s see, how to use BCP utility to Export Data from File to Table using SQLServer Authentication or security credentials.

>BCP TESTDB.dbo.BCP_Customers IN C:\bcp\Customers1.txt -Slocalhost -Usa -P123 –c

Now let’s see, how to use BCP utility using SQL Query Export Data from Table to File using SQLServer Authentication or security credentials.

>BCP "SELECT firastname,lastname FROM TESTDB.dbo.BCP_Customers where id<4" queryout C:\bcp\Customersqueryout.txt -T –c

Cheers!

Uma