"Steve" <anonymous@discussions.microsoft.com> wrote in message
news:12fc01c47b85$f1728ea0$a601280a@phx.gbl...
> Hi,
> I am building datamart but I need your expert advise to
> update the datamart.
> I get text file & load it into Staging Table. From
> Staging Table I load it into Dimension table
>
> Cust_Staging_Table
>
> Cust_id Cust_Name Cust_Add Cust_City
> 111 Sam 100 St. San Jose
> 222 Maria 7th Ave Hollywood
> 333 kathy 5th St. Dallas
> 444 Nicole 1st Ave Arlington
>
>
>
>
> Cust_Dimension_Table
>
> Cust_wk Cust_id Cust_Name Cust_Add Cust_City
> 1 111 Sam 100 St. San Jose
> 2 222 Maria 7th Ave Hollywood
> 3 333 kathy 5th St. Dallas
> 4 444 Nicole 1st Ave Arlington
>
>
> Now I get a new text file for customer table with info.
> changed. For example Maria's address has changed. I
> truncate the previous info in Customer_Staging_Table &
> load this new info from text file.
>
> Cust_Staging_Table
>
> Cust_id Cust_Name Cust_Add Cust_City
> 111 Sam 100 St. San Jose
> 222 Maria 44th Ave Burbank
> 333 kathy 5th St. Dallas
> 444 Nicole 1st Ave Arlington
>
> To make this info. in sync with my Cust_Dimension_Table,
> I use the following update statement
>
>
> Update Cust_Dimension_Table
>
> set
> Cust_Dimension_Table.Cust_Name =
> Cust_Staging_Table.Cust_Name
>
> Cust_Dimension_Table.Cust_Add =
> Cust_Staging_Table.Cust_Add
>
> Cust_Dimension_Table.Cust_City =
> Cust_Staging_Table.Cust_City
>
> where
> Cust_Dimension_Table.Cust_id = Cust_Staging_Table.Cust_id
>
> In my datamart I have more than 200000 records. Any
> info. can change regarding a particular column. Is there
> an effecient way of updating the dimension table. (Shall
> I use views/temp tables). Can anyone please show me the
> effecient solution for update with above example.
>
> Thanks
>
> Steve
>
>
>
>
>
>
>