One Star

How to validate data while inserting into table

Hi,
While inserting data from source to table i need to check whether the particular record is present in the destination table, if the particular record exist then no action should be done on the data, if record does not exist then that record has to be inserted. How to do this, Please share if any sample transformation is available

Thanks in Advance
Siva
1 REPLY
One Star

Re: How to validate data while inserting into table

You have few options to do this.
First generic one is to have a tMap component with a join to your destination table (lookup).
Then you could have a conditional output flow with "inner join reject" set or your custom condition.
This option might be little bit slower than the other as it requires extra database call to read the data.
Other option is dependent on your destination database as different output component have different options.
I would choose this options from the Action on data drop down:
tMSSqlOutput = "Insert if not exist"
tMysqlOutput = "Insert Ignore"
tOracleOutput = "Update or insert"
tSybaseOutput = "Update or insert"
"Update or insert" should work even if you do not have a column set for an update, but you might have to have a schema key specified.