BCP Export

I found this one in some old code, and decided to post it.

Not rocket science, but very handy sometimes.

The below is a "fixed width" export, using bcp.

The syntax sugar for "fixed-width" is the cast to a char(SomeNumber) where SomeNumber is your width of course.
The other gotcha is the -t argument (with nothing specified) which overrides the default column delimiter of a tab.  Aka, below I’m specifying nothing for the -t argument so the default ‘tab’ does not get inserted.  If you want to see how it works, specify the -t argument like this:
-t:***:
Then it will be obvious what the -t argument does.  Remember, there is a default value, so if you don’t want the default, you must override it.

The other syntax sugar is that there is no space after the -S argument.  As seen below (-SMyServerName\MyInstanceName).

bcp.exe  "SELECT cast(LastName as char(50)) , cast(FirstName as char(50)) , cast(MiddleName as char(50)) , cast(Suffix as char(50)) FROM MyAdventureWorksDB.Person.Person ORDER BY NEWID()" queryout PeopleRock.txt -c -t -T -SMyServerName\MyInstanceName

MyAdventureWorksDB is the name of the database.
Person (the first one) is the schema. 
Person (the second one) is the table.

I am using the AdventureWorks2008 database I got from codeplex.

The Order by NEWID() is completely useless and can be removed.  However, if I’m testing the export (most likely importing the data through another system) I like to leave that in so I don’t get the same data or same order every time when I am testing the output.

The other command line argument of interest is queryout.  I’ll let you see what that one does in the microsoft url below.

Here is the full bcp command line list:
http://msdn.microsoft.com/en-us/library/ms162802.aspx

Advertisements
This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s