Informatica's Update approach allows you to delete and update records without using SCD.

Joined
Apr 3, 2023
Messages
1
Reaction score
0
Please fix this problem for me:

I have a flat file with data from three places. ( Ahmedabad, Bangalore & Chennai ). This info is being loaded into a database. Insert is easy. However, it should insert/update the new entry and delete from the prior table whenever I change my location in the source, for example, from Bangalore to Chennai, Ahmedabad, or vice versa.
Example:

Location

From Bangalore to Chennai (Updated location) Chennai Ahmedabad

Following Informatica transformation:

Chennai, Chennai, and Ahmedabad
 
Joined
Mar 31, 2023
Messages
95
Reaction score
8
It seems like you are looking for a way to update the database with new data and delete the old data whenever there is a change in the location source.

One way to achieve this is to use a merge statement or an upsert operation in your database. This will allow you to insert new data, update existing data, and delete old data all in one transaction.

Assuming you are using a relational database like MySQL, you can use a merge statement similar to the following:
SQL:
MERGE INTO locations AS target
USING (
    SELECT 'Chennai' AS location
    UNION ALL
    SELECT 'Ahmedabad'
) AS source
ON target.location = source.location
WHEN MATCHED THEN
    UPDATE SET last_updated = NOW()
WHEN NOT MATCHED THEN
    INSERT (location, last_updated)
    VALUES (source.location, NOW())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
This statement merges the data from the source table (which contains the new locations) with the target table (which contains the existing locations). It updates the last_updated timestamp for existing locations, inserts new locations, and deletes locations that are no longer present in the source table.

You can run this merge statement periodically using a cron job or a similar scheduling mechanism to keep your database up to date with the latest location data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,708
Messages
2,569,346
Members
44,650
Latest member
LuckyVivo

Latest Threads

Top