Data Compare for Non-Existent tables

juliajuliabelljuliajuliabell Posts: 10
edited January 4, 2010 5:06AM in SQL Data Compare Previous Versions
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?
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • juliajuliabelljuliajuliabell Posts: 10
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • juliajuliabelljuliajuliabell Posts: 10
    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.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    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
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.