Wanna merge files recursively from the DOS command line?

SQL Files All Over The Place – Problem!

I do a lot of grunt work in DOS.  Also, I have dozens of SQL scripts in multiple folders that need to be run sequentially in order to recreate the database, create the database objects, and seed the initial data in the system.  I can run them one at a time, eating up half an hour or more of my time, or I can merge them in the correct order and run them as a single script against the SQL Server database.

I’m assuming you’re here because you have a similar problem.  I’m going to walk you through a series of small steps to get to the end result which will allow you to successfully merge multiple SQL files into a single script in an order you specify via how you number/name your directories and scripts.

Step 1 – File/Directory Naming Convention

I’m assuming you want to run your scripts against your database in order, right?  You don’t want to add stored procedures before you add the tables and you don’t want to add your seed data before you add the database, right?  In my case, I have 3 directories (simplified for this blog post), named as follows:

1. 001_DatabaseCreation
2. 002_Stored_Procedures
3. 003_SeedData

And within those directories, I have SQL scripts numbered in the order I want them run as well, like so:

1. 001_CREATE_DATABASE.sql
2. 002_CREATE_SCHEMA.sql
3. 003_GRANT_SCHEMA_ACCESS.sql
4. 004_CREATE_TABLES_INDEXES.sql

Step 2 – List the directories in order

Sometimes, when you run a DIR command, the results are not alphabetical.  Notice that even though I have my directories numbered sequentially, the DIR listing is not alphabetical.

This is an easy fix.  I just change my DIR command to:

dir /s /b /o:n /ad

And the results are listed as I expect them.  Note I added some parameters here to give me a bare result that I can feed into the next section of this post.

• /s returns the full directory path in the result
• /b specified bare format with no header  or summary
• /o:n forces the alphabetical result
• /ad ensures only directories are returned in the result

The end result looks like this:

Step 3 – List those files in order!

The same issue holds true for file listings within the individual directories as well.  Even though the SQL files are numbered, a DIR listing will most likely show them out of order.  Like so:

Again, this is fixed just like we did for the directory above, just removing the /ad option to force folder listings:

dir /s /b /o:n

It returns a listing of files in the order I want to run them:

This is good.  Now we can list our directories in alpha order as well as the files in a directory in alpha order.  This will come in handy below.

Step 4 – Loop Through Directories

This step introduces the DOS FOR command. For allows you to execute a command when a condition is met.  In this case, for now, we just want to do 2 things to make sure we understand how it works:

1. Get a list of folders
2. Write those folders to a file

Here is the syntax – we’ll go over what this means shortly:

for /F "tokens=*" %d in ('dir /s /b /o:n /ad') do (echo Found Folder "%d" >> output.txt)

Ok great!  You just ran that and it worked!  It outputted the names of your folder, in the right alphabetical order, to a file.  Congrats.  But what the heck does this all mean?

In essence, the above says in pseudo-English: “For each directory name that this DIR statement returns, write ‘Found Folder ‘ and the directory name to a file called output.txt.”

I’ll run through each option here:

• for = Runs a specified command for each file in a set of files.
• /F = means the fileset is one or more file names. In our case, directory names.
• “tokens-*” = specified which tokens are passed into the variable %d through each iteration.  Without this, the next option, %d, would always be ‘dir’ and not the actual directory name.
• %d = the variable for the directory names that will be populated in each iteration of the loop.
• in (‘dir /s /b /o:n /ad’) = this is the command that returns the result set, the list of directories that will be looped through
• do = do!  When you loop, do something and that something is….
• (echo Found Folder “%d” >> output.txt) = write the output to a file

Step 5 – Loop within a Loop (aka – I’m dizzy)

Ok here is where we get a little crazy.  We’re going to add a loop within that first loop!  Instead of just writing the directory names to a file, we’re going to write a list of files in each directory to the file!  This means we’ll be looping through each directory for files as we loop through the directories. Here’s the syntax:

for /F "tokens=*" %d in ('dir /s /b /o:n /ad') do (for /F %f in ('dir /b /on "%d\*.sql"') do (echo "%d\%f" >> output.txt))

Whoa!  It worked!  Open up output.txt and take a gander.  You’ll see every SQL file in your directories listed alphabetically by alphabetical directory!

Step 6 – Actually merging all the SQL files into a single file

Ok – all the above has been a precursor to this.  Let’s put all this together and make a single SQL script I can run on my database, ok?  It’s pretty simple from here.  Here’s the command:

for /F "tokens=*" %d in ('dir /s /b /o:n /ad') do (for /F %f in ('dir /b /on "%d\*.sql"') do (type "%d\%f" >> output.txt))

HOTCHEMOMMA!  I now have a SQL script I can run against my database!  Instead of just using ECHO to output the filename, I’ve switched to TYPE to open and read each line in the file output to output.txt.

OH NO!  I’m getting errors when I run the script!

Yeah.  Crap.  The merge of the files has a couple of issues, right?

• If there isn’t an empty line at the beginning or end of one of my files, the merge just adds onto the last line!
• I get weird errors like “Incorrect Syntax: Create Procedure must be the only statement in the batch.

This happened to me too.  And there are 2 fixes:

1. Edit EVERY one of your files to have a blank line at the beginning and end, as well as making sure EVERY file begins or ends with GO
2. Modify your DOS command to account for this.

Naturally, I opted for #2 and I changed my script to this:

for /F "tokens=*" %d in ('dir /s /b /o:n /ad') do (for /F %f in ('dir /b /on "%d\*.sql"') do (echo. >> output.txt & echo PRINT 'Running Scripts in %f' >> output.txt & echo GO >> output.txt & type "%d\%f" >> output.txt))

This pulls everything together!  It creates a single SQL file I can run on my database, makes sure every file starts with a blank line and a GO statement, and even gives me a SQL PRINT statement showing me where it is as it runs.

Next Steps

I simplified this post for (relative) brevity, but there’s a lot you can do with this.  The above is a single line DOS command.  You can add this to a batch script and pass in parameters, add additional file handling, and more.  For example, see below for an earlier version where I not only created the merged file, but executed it against a MySQL database from the command line.  Anyway, enjoy and feel free to use, improve, and do as you will with it.

Note: I haven’t tested this with multi-nested directories.  My environment is 2-level deep directories.