August 6, 2009 at 11:35 am
I am going though some sample questions of db tests in my job and came across a question that i am stuck on. the question is shown below:
1. Create 3 databases, LoadA, LoadB, and DBManager.
LoadA will be the source database, LoadB will be the destination database and DBManager is the controlling database.
2. Create 3 different tables with 12 columns each in database LoadA. The data types in atleast one table must include the following: text and dbtimestamp, table names/column names do not matter.
3. In the DBManager database write t-sql code which will do the following:
a) create tables from LoadA in LoadB (that do not already exist in LoadB)
b) compare the table structure and identify any changes that have been made between LoadA and LoadB
c) Load the initial data from LoadA to LoadB
d) Load an incremental Load from LoadA to LoadB
The code should not have any table names hard coded as the one piece of code will be required to load all tables.
The data base should not be hard coded because DWManager should be able to load data from any specified database to another specified database.
HINTS
- The tables in LoadB will need to have some sort of DATE column to identify when the data was loaded into the database.
- The incremental load also needs to take into account the fact that if a record is deleted from the source database, that deletion is identified in the destination database.
How would i script this with TSQL? What is an incremental load and how does one database control what happens in another database. Any ideas whould be much appreciated.
Thanks
August 6, 2009 at 11:42 am
What would this test be for?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
August 6, 2009 at 11:43 am
Moving from informatices to the datawarehouse team in my dept
August 6, 2009 at 11:51 am
This would all be possible with dynamic SQL and querying the schema tables.
Querying sys.tables and sys.columns, you could pretty easily generate create scripts for tables missing from the target database. You could also create alter table scripts for missing/modified columns.
Once you've done that, you'd again hit the schema tables and generate upsert commands for each table. With some of the features of SQL 2005, like Except and Intersect, you wouldn't need a datetime column (mentioned in the hints). With SQL 2008, you could use Merge to simplify the upsert.
It would take some time to build and test the script. Not something I'd do in a few minutes. Given an hour or two, I could probably manage it and make it robust enough that it would be able to deal with constraints, handle errors, prevent data truncation, and could even generate linked servers if you told it to.
Outside of an academic exercise, I'd never bother. RedGate and ApexSQL both produce software that will do this for you more easily, and you don't have to re-invent any wheels for it. It's not even expensive.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 11:56 am
Thanks for that. will try that. What is an incremental load?
August 6, 2009 at 12:18 pm
Look up "incremental" in a good dictionary. Not trying to put you off, but it's pretty straightforward once you've done that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 4:00 pm
thanks!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply