Merge a large stack of SQL files into one file?

samjones11

(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!!!

David Atkinson

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

samjones11

Thanks a bunch! I will give it a try!

samjones11

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?

David Atkinson

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)

samjones11

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!

David Atkinson

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

samjones11

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

David Atkinson

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)

tgould

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