Quick and easy sql server to flat file

There are many times you need a flat file (csv for instance) to move from one db type to another. SQL Server bcp is just the ticket and combined with select into lets you create just what you want for those pesky data format translations.

The trick is to create a table using select into then use bcp to output it. Any data transformations you need to make (i.e., changing date formats) should be done when you build the temp table. In the simplest form the steps are:

1. select * into tempdb..mynewtable (This both creates mynewtable and puts the data into it.)
2. from a dos command line: bcp tempdb..mynewtable out mynewtable.csv -c -S server -U user -P password -t',' The -n flag tells SQL Server to just ouptut the data in character format.
3. do what you will with the csv file. Import it into a spreadsheet, load it into mysql using load data, or load it into Oracle using sqlldr.

So one issue you may run into, especially with dates, is that one system's format isn't necessarily the others. The easiest way to handle this is to make your target tempdb data column match what you need. For SQL Server just use the convert function and one of the many date flags. To do this you may have to create your tempdb table manually then simply insert data into it.