Here's my situation.
I have a data warehouse. It gets loaded with data from time to time. The design of the warehouse is such that when it is loaded, all previous data is wiped out.
Without telling a really long story I need to retain old data to use as a weapon against my enemies.
Anywho... what I've done is created a database whose tables are structural duplicates of some tables from the warehouse with a time dimension added. When the warehouse is load is complete I do a select insert into my historic database (with the time of the load on each record).
What I want to do is created a query that will identify differences between the current warehouse data and data in my historic database based on a specific date / time.
It's simple enough to write something like...
select * from currentdata C
inner join historicdata H ON C.ID = H.ID
where NOT C.column1 = H.column1 AND
...
NOT C.column104 = H.column104 AND
H.LoadDate = @somedate;
I am curious if there is another way to accomplish this without having to specify each column in the query?? These tables have hundreds of columns so it's kind of a pain. If it comes down to it I?ll write a little console app to create the query but am interested in other approaches before I make the plunge.
I have a data warehouse. It gets loaded with data from time to time. The design of the warehouse is such that when it is loaded, all previous data is wiped out.
Without telling a really long story I need to retain old data to use as a weapon against my enemies.
Anywho... what I've done is created a database whose tables are structural duplicates of some tables from the warehouse with a time dimension added. When the warehouse is load is complete I do a select insert into my historic database (with the time of the load on each record).
What I want to do is created a query that will identify differences between the current warehouse data and data in my historic database based on a specific date / time.
It's simple enough to write something like...
select * from currentdata C
inner join historicdata H ON C.ID = H.ID
where NOT C.column1 = H.column1 AND
...
NOT C.column104 = H.column104 AND
H.LoadDate = @somedate;
I am curious if there is another way to accomplish this without having to specify each column in the query?? These tables have hundreds of columns so it's kind of a pain. If it comes down to it I?ll write a little console app to create the query but am interested in other approaches before I make the plunge.
