Data Compare for Non-Existent tables

juliajuliabell

If a table exists in the source database, but not in the target database, is it possible to use a combination of SQL Compare/SQL Data Compare to script the schema and the data in one step?

David Atkinson

juliajuliabell wrote:

If a table exists in the source database, but not in the target database, is it possible to use a combination of SQL Compare/SQL Data Compare to script the schema and the data in one step?

Can I ask what problem you're trying to solve in your scenario so we can better advise you? SQL Packager can create a script in one wizard, but maybe you're looking to do something else?

David Atkinson
Product Manager
Red Gate Software

juliajuliabell

Hi

Essentially I am trying to create an upgrade script for my database which I can then reuse on multiple servers. So I have one database which is the old version and one which is the new version. I can compare these databases with SQL Compare and SQL Data Compare to generate scripts that will transform any database that is the old version into the new version.

However if one of the differences between the old and new database versions is a new lookup or definition table, both the table schema and the table data needed to be scripted out as part of the upgrade. I can't figure out how to do this. Can I use SQL Packager for this?

Hope that's clearer

Julia.

David Atkinson

That's tricky unless all your target databases are identical. It's a big ask to expect a rerunnable script that is guaranteed to work against targets that may have subtly different schemas and data.

If you really need to automate this, one thing that you might consider is to use the SQL Comparison SDK to programmatically compare and synchronize your targets. Because the comparison is being done afresh on each target, the script will be correct each time. However, if you are updating production servers, I would strongly recommend you do this on a case by case basis using a script that has been carefully reviewed.

However, if you know that your targets are all identical, you should be able to generate a single script with SQL Compare / SQL Data Compare, or SQL Packager, which can be run on each of them.

David

juliajuliabell

Hi David

Yes, all the databases which I will be using my upgrade script on are exactly identical. Sorry I should've been more specific.

I can script the schema of the new definition table using SQL compare, but I can't work out how to script the data. SQL data compare doesn't seem to be happy to script the data for a table that only exists in the source database.

Can you give me more info on how I can do this using SQL Compare, SQL Data Compare or SQL Packager?

Julia.

David Atkinson

You're right, SQL Data Compare will require the table to exist in the target, so you'll have to generate the schema script first, apply it to your target database (please do this on a 'test' database!), then generate the data script as a second step. Please give this a go and let me know how you get on.

David