topic Best Practices for storing and executing SQL statements with a change log? in SAS Programming

Best Practices for storing and executing SQL statements with a change log?

Mishka1 — Fri, 02 Sep 2011 19:00:28 GMT

I appologize if this is misfiled. I'm new here.

I'm looking for some direction to go with the below project. Any guidance or pitfalls to avoid will be appreciated.

I have about 100 select SQL statements that query different databases. My project requires them to be run each day and results stored in one table for easy user access. These queries often change. When they do, I want to be able to capture the change history so that if I ever wanted to run logic from say, "this date last year--but with this modification" I can do that for modeling purposes.

So it's at least 2 parts. One, what is the best way to store hundreds of SQL queries so that they can be executed? Two, how do you integrate a dated change log into the process?

Thanks in advance for your advice.

Best Practices for storing and executing SQL statements with a change log?

LinusH — Mon, 05 Sep 2011 08:10:35 GMT

First part: depends on the nature of your jobs. Do you mean that you have hundreds of SQL that are loading data in the same table? Are they dependent of each other? If the SQL's are quite complex and have dependencies, maybe Data Integration Server/Studio should be considered. If this approach is too heavy, implementing them as Enterprise Guide project could be one alternative - which at least give you some graphical view of the logic.

Second part: use a 3rd party system for this. Maybe your site already has one. Data Integration Studio (9.3) has direct integration with Concurrent Versions System (CVS) and Apache Subversion (SVN). These tools can use packages from DI Studio, EG projects or plain text-files with code (i.e. SQL).

Linus

Best Practices for storing and executing SQL statements with a change log?

Mishka1 — Tue, 06 Sep 2011 12:40:27 GMT

I do have hundreds of SQL statements loading data to one table and they are all independent of each other. I am going to look into Data Integration Studio with CVS as a possible solution. Thank you.