Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: How to avoid existing records in output table?

  1. #1
    Join Date
    Oct 2015
    Posts
    1

    Default How to avoid existing records in output table?

    Hello, I need to insert in an output table some records selected from an input table. In the output table I have a unique constraint in a specific field, so when I try to insert already existing names, it gives me an error of course.
    I want to ignore those records from the input table that already exists in the output table or pass the unique constraint error for that record that is being inserted.
    How can I manage this? What step should I use and how should I use it?


    Thanks

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You could set up error handling on your Table Output, which would let the valid new record be inserted, while catching the rows where the unique already exists.
    You could set up your input to filter out data that already exists.
    You could do a DB lookup to see if the data is already in your DB, and filter out those that do exist.
    You could use Insert/Update (warning: SLOW) to insert the data, and if it already exists, update it.

    As you can see, there are many ways of doing things in PDI.

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    And sometimes we are just tired to point out that you always should start by giving the reason why a single SQL statement doesn't solve your database problem.
    Let's assume you forgot to tell us that SQL is no way in your case.
    You could calculate the difference set using Merge-Rows, in addition to all the ways gutlez pointed out.
    Which way to go depends very much on the rownumbers involved.
    So long, and thanks for all the fish.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.