HI

1. There are two OLTP Source System tables Table1 and Table2_history . Please refer the following scenario

2. Case 1 : When a event with ID1 occurs a record gets inserted into Table1. When the event is cleared the record with ID1 gets deleted from Table1 and it gets inserted in Table2_History table.

3. Table2_History has the record with ID1 now.

3. Case 2 : There records remains in Table1 and does not get inserted into table2 till the event gets cleared for the particular record. I mean there are most of the records that are not present in Table2_history  and not in Table1

The problem is that I am not able to join both tables based on their ID's. I need to move the information of both the Tables into destination (FACT) table. Since these are transactional tables so I need to create a FACT

Can anyone please suggest me how should I manipulate the data to move the data from both the tables present in the Source (OLTP DATABASE)  to destination(DATAWAREHOUSE).

Thanks

• Edited by NEERAJ DUBE Friday, June 26, 2015 4:26 AM

Hi Neeraj Dube,

I don't really understand Case 2 or what your problem looks like.

If you have got disjunct IDs in each table it shouldn't be a big deal to transfer data separately to your DWH or use an UNION ALL statement, shouldn't it?

If you happen to have the same ID in both table and need to implement a specific logic (e.g: Table1 has always precedence) you could use a MERGE Statement or insert from Table2_History first without the IDs from Table1 and then insert from table1.

Maybe I completely misinterpret your inquiry. Thus please help me with some more examples / sample data.

Cheers

Martin

As you are moving Records from Table1 to Table2 there will be no matching records in both Tables so a join is pointless in this scenario.

Create a View that combines your Records: -

Create dbo.CombinedHistory AS

BEGIN

Select * From Table1

Union All

Select * From Table2Histroy

END

And use this view as a data source to transfer to the data warehouse.

Hello - If i understood correctly, you will have these records present in either Table 1 or Table 2 and you want to insert them to your fact table right ?

So I think you can achieve it by just using UNION operator, say for example:

SELECT * FROM 
(SELECT ID1 FROM DBName.dbo.Table1
UNION
SELECT ID1 FROM DBName.dbo.Table2)A

I do not suggest using UNION ALL since it may cause duplicates if for some reasons ID1 exists in both tables If you are sure that won't be the case ever then you can either use UNION or UNION ALL

Hope this helps

Sounds to me like you don't wan records until they're written to the history table, as they're likely 'in process' transactions.

I'd just slurp up the history, and go from there.

Hi,

Thanks for the reply,

I am not using union all for moving the data to destination. I have implemented the following Logic :

1. Moved the data from Source (Table1) to Staging Table (STG1)

2. Moved the data from Staging Table (STG1) to destination table (FACT)

3. Moved the data from Source (Table2_history) to Staging Table (STG2)

4. Moved the data from Staging Table (STG2) to destination table (FACT)

Due to this I am able to populate the data from both tables Table1 and Table2_history to destination table (FACT)

with the help of Staging tables.

Please suggest how will be performing the incremental load/delta load into destination table using the method implemented