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.

couldn't resist this picture

In case of network connectivty issues.....check your cable.

Why I love this job.

I'm just getting started using Oracle. So, naturally I need a nice visual client to work with. Even though I run a Mac it's easier to run this on my Parallels Windows/XP session due to VPN issues (don't ask). So, off I go to install a client.

Get Oracle Insta-client 11.01.

Get sqlplus too while I'm at it.

Find and change environment settings in Windows.

Get tnsnames.ora file from a unix box that I know works.

Test sqlplus. Yippee it works!

Get Toad.

Toad crashes; it won't support 11.x. Gotta downgrade to 10.x

Get another Oracle Insta-client 10.2

Change environment settings in Windows again.

Start Toad.

Uh oh. missing DLL.

Find DLL on web, download, install, reboot.

Yea! Toad works and connects.

Life should not be this hard.

database diagrams

At one time or another we have all been handed a new system and its related database(s). How do you wrap your arms around the entities and grasp the main concepts. For me, nothing beats an ERD and although Erwin and Microsoft Visio do good jobs at presenting these, the best product I've worked with for reverse-engineering a database is SchemaSpy. And it's free! As in beer. Check it out the next time you need a diagram. (I have no relationship with the author of SchemaSpy; I just think the product is really cool and wish I could write Java like this.

http://schemaspy.sourceforge.net/

do you really need that left join?

I've been working with mySql and ruby on rails developers for the last several years and found that the generated code seems to like left joins. Something like

select * from foo

left join bar on foo.id=bar.foo_id

In most cases I've found they rarely need the left join and a simple eqi-join will work as well and significantly reduce the noise in the mysql slow query log. Check those slow queries and if you see lots of left joins and left outer joins have a conversation with your friendly developer.

Random thoughts for the dba on various platforms.

sql server (and others) insert returns duplicate key and you are sure it's not duplicate. Check your collation set; case insensitive character sets make Foo, foo, fOo, or FoO all the same and an attempt to insert any of these values in a unique indexed column will give you the useless "duplicate key" error.