I was working with a cutomer today who does an annual archive of their database.  They backup the database and then delete all transactions that are over a year old form the current database.  So, to do a multi-year report they need to combine data from different instances.  They restore a prior year instance whenever they want to look at old transactions.  They asked me if there was an easy way to combine transactions from two separate instances into one report.

Crystal’s linking window is great for linking tables but not as good for appending.  So this is one of the main reasons I end up using SQL commands – to use UNION ALL to append the records of one table to another.   But I wasn’t sure if I could do a UNION with tables from two different database instances.  Fortunately they were on the same server.  Linking separate servers takes a bit more work.

So I wrote a simple query for one instance, pasted it in twice and put UNION ALL between them.  Then I tweaked the second query to use the name of a different instance:

SELECT CAL.ONDATE, CAL.RECTYPE, CAL.DURATION
FROM GM6.dbo.CAL CAL

UNION ALL

SELECT CAL.ONDATE, CAL.RECTYPE, CAL.DURATION
FROM GM62011.dbo.CAL CAL

This worked fine. Then the customer asked if they could change the names of the backup instances. Rather than make the user go into the SQL to change the name, I made it a parameter.  I added two parameters to the command and put the parameters in the place of the database instance names.  Now the user is prompted for the instances at runtime.  I never had occasion to use a parameter like this, but I wrote about it as an idea for selecting tables in an older blog post.  As you can see, you can replace ANY word in a SQL Command with a command parameter.  This includes table names, join types, and fields as well as the literal values.

If you have a project like this and you need some help, give me a call.