Merge a large stack of SQL files into one file?

samjones11samjones11 Posts: 14
edited July 9, 2009 1:32PM in SQL Compare Previous Versions
(If this is the wrong tool, tell me the right one!)

So our devs work like this:

CreateDB_001.sql // main file, creates the main tables
CreateDB_002.sql // these are all little adds and mods
.... for another 100 .sql files


To roll out a new vanilla system, I would rather not:

a) Execute these all by hand
b) figure out a script to execute them all in order

So my question is:

Does this tool (or some other tool) have the ability to merge a whole directory of .sql files into one big script?

Thanks!!!
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Yes.

    SQL Compare Pro should be able to read all creation sql files in a folder and order them in correct dependency order, which can either create a script to create a new database or a script to synchronize a pre-existing target.

    If you're generating a script to build a new database you'll have to create a 'blank' database yourself and compare against this.

    Please give this a go and let us know if you have any problems.

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • samjones11samjones11 Posts: 14
    Thanks a bunch! I will give it a try!
    · Share on Twitter
  • samjones11samjones11 Posts: 14
    No joy.

    I have tried to:

    a) Compare a dir of scripts to a db

    b) Compare a dir of scripts to a dir of a single, composite script


    It all fails.

    (Ack, I was going to post a screen shot, but this form seems to not allow attachments?)

    The error is: 
    SQL Compare Aborting
X Registering Databases

...
A duplicate definition was found for the table....


    Now what?
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Does it tell you which table it thinks is duplicated? If so, is this believable or is SQL Compare at fault? Can you confirm that you're using SQL Compare 8.0?

    If you're able to zip up and email the script to me I will get someone to take a look.

    David Atkinson
    Red Gate Software
    (David dot Atkinson at Red-Gate.com)
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • samjones11samjones11 Posts: 14
    Hello!

    Yes, it does tell me the problem table.

    Yes, the problem compare does actually have two CREATE blocks (a problem).


    The dir of scripts has this:

    createdb_001.sql
    createdb_002.sql

    etc

    Then it also had

    createdb_008_fullscript.sql

    which duplicated the stuff in the 007 and earlier .sql files...

    Ergo the issue.

    So I dropped the ...fullscript files, and am trying to do a compare of the dir of sql scripts to another dir (which has a single, composite file I built by hand).

    CURRENT PROBLEM

    I am trying to do a compare a dir of sql scripts to another dir (which has a single, composite .sql file I built by hand).

    This is failing:
    SQL Compare - Aborting
X Registering databases
  scripts.parts-runningpostprocessing actions

I Failed to compare two elements in the array


    I am running: SQL Compare 8.0.0.965 (from the help | about)


    So far, I have been unable to get SQL compare to do anything.... Please help!
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    I would suggest you narrow down what's at fault by trying to compare the set of scripts against a blank folder or empty database to see whether this will compare synchronize correctly.

    If this works, try comparing your single script with a blank folder or empty database.

    Let us know how you get on with this.

    David
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • samjones11samjones11 Posts: 14
    David Atkinson wrote:
    I would suggest you narrow down what's at fault by trying to compare the set of scripts against a blank folder or empty database to see whether this will compare synchronize correctly.

    Cool idea (comparing to a blank folder... excellent, but never would have thought of it!)

    Results:

    a) My single file parses fine, and gives expected results vis-a-vis schema

    b) My folder of files fails, with same error:
    SQL Compare - Aborting
X Registering databases
  scripts.parts-runningpostprocessing actions

I Failed to compare two elements in the array
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Is there any chance you could send us a copy of the scripts (zipped?) so we can debug this here? Without this, it's hard for us to pinpoint what the issue might be. You could try to remove various script files and narrow down the one that's at fault?

    David

    (David dot Atkinson at Red-Gate dot com)
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • tgouldtgould Posts: 1
    Is there something in the command line or the API/SDK to take all creation sql files and generate a single file?
    David Atkinson wrote:
    Yes.

    SQL Compare Pro should be able to read all creation sql files in a folder and order them in correct dependency order, which can either create a script to create a new database or a script to synchronize a pre-existing target.

    If you're generating a script to build a new database you'll have to create a 'blank' database yourself and compare against this.

    Please give this a go and let us know if you have any problems.

    David Atkinson
    Red Gate Software
    · Share on Twitter
Sign In or Register to comment.