Archives / 2007 / December
  • [SQL] Tools I Can't Live Without Right Now

    I've been doing loads of SQL crunching lately, staging, versioning, and preparing for a pretty large migration job later this year which involves a great deal of schema and data changes in SQL Server 2005.

    A tool which has been saving me a great deal of time is Red Gate's SQL Compare. As the names says, it's able to compare everything you're interested between two databases in when it comes to SPs, tables, functions, roles and so on. You can also generate SQL scripts which wil handle the migration from one version of the database to the other without (if possible) corrupting the data. The tool is dead easy to get into I think - no need to read any manuals or anything, just new, select db1, db2 and compare. Check, uncheck and migrate.

    Red Gate also has a SQL Data Compare tool which compares data in two identical databases and helps you move data between them.

    If you just want to create a bunch of "INSERT INTO ..." SQL statements from existing tables, perhaps you should look at the stored proc Narayana Vyas Kondreddi has made available from his code library:

    This procedure generates INSERT statements using existing data from the given tables and views. Later, you can use these INSERT statements to generate the data. It's very useful when you have to ship or package a database application. This procedure also comes in handy when you have to send sample data to your vendor or technical support provider for troubleshooting purposes.

    To show you how simple this one is, the easies way to run it is just:

    EXEC sp_generate_inserts 'titles'

    and out comes a bunch of INSERT statements which you can copy/paste into your batch files. I love small tools like that.