SQL Server Performance Forum – Threads Archive

Green Warehouse

Hello, I’m pretty new at SQL Server, so please bear with me [^].
I have 4 customer tables each of which have different Primary keys (customer
numbers). Each of these tables is receiving new records daily. I have been given the task of creating a master warehouse which has it’s own brand new primary key (identity). It should import all NEW records from those 4 tables on a nightly basis using DTS. Because there are going to be new columns in the warehouse which will change regularly, it is important that the table keeps the old data and it doesn’t recreate with each run of the DTS. Can someone suggest to me the best way to to import new records only to these tables and possibly a way to time stamp them upon importing.
Thank you for any help,
Justino

There are probably lots of ways to do this….here’s one in your master warehouse table, make sure you have the id from the original table stored somewhere, along with some identifyer to tell which table it came from. Index these if possible (together, they should form a unique index too)
then you can insert with a select where the sourcetable/tableid dont exist in the warehouse. or save the datecreated on your source tables and somewhere in your warehouse or db the last date extracted and then look for records saved after that date to add to the warehouse. (I dont like this method, but its another choice) You can add a field to your source tables thats a flag that translates to uploaded or not and then select on that flag and update the records you inserted into the warehouse that day. There are more ways, and I’ve used them for different projects, based on a few other factors and the user needs. chris

Thanks Chris, very helpful.

]]>