Moving a table using BCP and native format8
March 21, 2018 by Kenneth Fisher
You need to move a table from one instance to another with the following requirements:
- The instances are on separate domains. In fact, you might be shipping the table to a vendor or client.
- The destination is an actual SQL Server instance.
- Can be automated on either end.
- Quick and easy please. Assume limited knowledge on the recipients part.
The separate domains/companies is a big killer. This eliminates options like replication, linked servers, etc. We can dump the output to a CSV but that does have some limitations if you have odd characters. Not to mention it requires extra work depending on your creation method. The automation means no GUI. Combine that with Quick, easy and limited knowledge on the recipients part and I want a script or two that I can pass a user.
My go-to solution here is going to be BCP. For those of you who aren’t familiar with it, it’s a command line tool for moving data in and out of SQL. It’s fast and once you have it scripted, easy enough to run. There are a fair number of options so the scripting can be somewhat complicated but the help is quite good and with a little practice this isn’t a problem. I should point out here that my requirements may seem a bit contrived to point directly to BCP, but I did actually receive this exact request recently. Also, BCP is a nice go-to for a number of data movement requests.
If you have used BCP before you’ve probably been annoyed at having to either create a format file or go through and hit enter for each column to confirm the format. Native format avoids this problem. When you export the data it is dumped with the table format built in, and when you import the data, it’s read using the format of the destination table. This does mean that unless you do use a format file the source and destination table formats have to be identical.
I should probably point out here that you aren’t going to be able to just open a file created using native format using Notepad or Excel and expect to be able to read it.
In theory native mode is also faster although I wasn’t able to prove this conclusively.
Here is a simple example of the export using BCP in native format.
bcp "Person.Person" out "C:\temp\Person_Person.out" -S "(local)\SQL2016CS" -d AdventureWorks2014 -n -T
Pretty simple right? Here are the params in order.
- “Person.Person” – The table we want to export.
- out – Direction we want to go. This could also be queryout if the first parameter is a query and not a table name.
- “C:\temp\Person_Person.out” – Output destination.
- -S “(local)\SQL2016CS” – Source SQL instance
- -d AdventureWorks2014 – Source database (where the table is).
- -T – Use trusted authentication (you could use -U and -P for the username/password of a SQL login.
- -n – Native mode. You could also use -N for unicode native mode.
I should point out that the flags are case sensative so -D and -d are two seperate things.
Now I just need to give my vendor/customer three things. A script to create the table, the output file, and the following command which could just as easily be put in a bat file to run.
bcp "Person.Person" in "C:\temp\Person_Person.out" -S "(local)\SQL2016CS" -d AdventureWorks2014 -n -T
Notice how it’s almost identical to the first command? I’ve just changed out to in to tell it to import rather than export. Of course, the file location, server name, etc will need to be changed based on the environment but that’s pretty easy. I should also point out that this just does a bulk insert so if you are doing a complete replace of the table a truncate needs to be done first or you could have a problem with primary keys etc.
Either way, in the end, this is a very simple way to move data from one location to another.
Edit: I just had it pointed out to me that unless you use the -E flag you are essentially creating new values for your identity column. Might not be a big deal, but on the other hand if you are re-loading tables involved in foreign keys (or relationships without enforced referential integrity) you will probably run into some problems unless you load the initial values i.e. doing an IDENTITY_INSERT. Either way, you have been warned.
Category: Microsoft SQL Server, SQLServerPedia Syndication | Tags: code language, export, import, Microsoft SQL Server
8 thoughts on “Moving a table using BCP and native format”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
For “faster” I think your BCP OUT needs to use QUERYOUT and a Query (all columns, but sorted in Clustered Index oder) and then the BCP IN needs the “-h” hint (and some other details) that the file is pre-ordered by Clustered Index. “-h” can also be used for a TABLOCK
Perhaps? the script for Remote end could be in two parts – Before BCP and After BCP. “Before” to create table and Clustered Index. Maybe any Trigger(s) too (if they do anything by association with other tables …) and then “After” could be FKeys and other Indexes etc.
You should REALLY use -b switch when bcp data in to a table. Otherwise the complete file will be imported in one large transaction.
With -b switch, you can commit smaller batches during the import and thus not fill up the log file completely.
Nice! Thanks for the tip!
I am working on the BCP utility, trying to export about 400 tables to flat files my challenge is that the columns (headers) were not included in the files that wee dumped, Is that the normal behavior of BCP, any work around, what am I missing?
It’s normal. Here are a couple of links discussing a workaround:
Thanks I was able to put together the workaround and extracted the 400+ tables successfully
Glad to hear it!
[…] is a pretty handy little tool in your arsenal. I’ve talked about using bcp to transfer data from one instance to another before and this is another really great use for bcp. If you haven’t used it before bcp stands […]