Microsoft SQL Server - Exporting And Importing data using bcp.exe

Background

This is a quick guide on using the bcp.exe program to export and import data.

A while ago i needed to downgrade a customers 100GB database located in a MSSQL2008R2 server to MSSQL2005. And since downgrading is not supported i needed to do a export and import.

bcp is a command line tool that are installed together with Microsoft SQL Server that allowes you to export and import data.

The first time i did the export and import I run a problem when doing the import. I did not recognize the table definition of the bcp file. But this is solved by creating a format file and use that format file when doing the export and import.
 

SQL Connection

bcp.exe support both trusted windows authentication and SQL account.
To use window authentication you specify the -T parameter and then it will use the logged in user account for connecting to the database. If you want to use SQL account you use the -U and -P parameter.

Use the -S parameter to specify what SQL Server Instance and server you want to connection to. Eg -S (local)\MSSQLSERVER

Connect using Windows Authentication

bcp.exe <command> -S (local)\MSSQLSERVER -T 

Connect using SQL User

bcp.exe <command> -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword

Create Format file

The format file is a file explaning how the table is defined. the bcp tool can generate this file for you.

bcp.exe table format nul -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword

You will not have a formatfile.fmt and whenever you now export and import data for that table you need to include that format.

Export Data

Exporting data can be done in a couple of ways. By specifying the 'out' parameter you will export and entire table. If you want to export a result of a query and can specify the parameter 'queryout'. If you export using query you must be sure that the format file you use are matching the returned result.

Export and entire table

bcp.exe mydatabase.dbo.ValueTable out exportdata.bcp -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword

Exporting using query

bcp.exe "SELECT * from mydatabase.dbo.ValueTable WHERE valTime >= '2010-01-01'" queryout exportdata.bcp -f formatfile.fmt -S (local)\MSSQLSERVER -U sqlaccount -P sqlpassword

Import Data

To import data from a export file from bcp you use the 'in' parameter. And to make sure it reads the file correctly we also need to specify the format file to use with '-f'. with the -b parameter you can specify how large batches to use. Easy batch is imported and logged as a separate transaction that imports the whole batch before committed.

bcp myDatabase.dbo.ValueTable in "D:\ExportedData\exportdata.bcp" -b 1000000 -f formatfile.fmt -S (local)\MSSQLSERVER2 -U sqlaccount -P sqlpassword

More Information

Tagged:  

( Try Multi Commander, A file manager for professionals )

Copyright (c) 2015 - Mathias Svensson