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:
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: