Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Insert/update question

  1. #1
    Join Date
    Nov 2013
    Posts
    24

    Default Insert/update question

    Hi,

    I have table 1 with:
    `person_id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) DEFAULT NULL,
    `stamp_created` datetime DEFAULT CURRENT_TIMESTAMP

    Table 2 has:
    person_id int
    var_id int

    I filled table 1 using the transformation cvs file input -> table output.

    Since person_id is auto generated by DB, in my next transformation, I have table input (from table1) ->insert/update (person_id).
    But I am getting this error: Field var_id doesnt have default value.

    Should I fill table 2 with some dummy values and then perform an insert/update?

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    Why not just define a default value for var_id? Either that or you have to include a value for it in your insert/update.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3
    Join Date
    Nov 2013
    Posts
    24

    Default

    Darrell,

    I populated table 2 with some default values(using inpu cvs file -> table output transformation) before calling the table input->insert/update. so var_id has some values already in the table. I just want the person_ids picked up from table 1 and inserted/updated into table 2.

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

    Default

    What's the goal?

    If your input files have no relation to each other, how can PDI guess it?

    If you're just looking to build some dummy data for experimentation, after your table output 1, put a Select Values (so you only get the valid person_id's), then a "Generate random value" and then table output #2.

    Note: This setup will only create one row in Table 2 for each row in Table 1, so may not be valid dummy data for your use-case.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Nov 2013
    Posts
    24

    Default

    The goal is populate multiple tables from a given input file.

    I have table 1 with:
    `person_id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(255) DEFAULT NULL,
    `stamp_created` datetime DEFAULT CURRENT_TIMESTAMP

    Table 2 has:
    person_id int
    var_id int
    var_info int

    I populated table 1 from an input csv file. But since the person_id is auto generated by the database, I need to read it and update Table 2 with those values.

    What's the best way to do this? Both person_id and var_id in table 2 are primary keys.

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

    Default

    But HOW is PDI supposed to know what the relation between the data in Table 1 and Table 2 is?

    If you have another CSV file (lets call it File2.csv) that has:
    username | var_id | var_info

    Then we can look up the username in Table 1 and put the ID in table 2

    But if your source data *HAS NO INFO* PDI cannot generate real data.

    Walk us through the steps you would do if you were doing this manually. Maybe then we can tell you how to do it in PDI.
    Last edited by gutlez; 11-26-2013 at 01:59 PM. Reason: Clarification of request
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  7. #7
    Join Date
    Nov 2013
    Posts
    24

    Default

    Here's what I am trying to do:

    I have File1.csv and I populated table 1 with this.
    I have File2.csv and I populated table 2 with this.

    Now I want to take person_ids generated by the database in table 1 and update table 2 with this.
    I tried a database lookup on table 1, but it is returning only the first person_id value.

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

    Default

    But what are the CONTENTS of File2.csv?

    If File2.csv has nothing to relate the information back to the information in File1.csv, there is nothing that can be done.

    I'll repeat the prior request: If you were going to do this data entry by hand, what would you do?

    Example:

    1. Read File1
      1. For each line in File1, enter the data directly in Table1

    2. Read File2
      1. For each line in File2, look up the "username" column to find a matching id in Table1
      2. Replace "username" in File2 with the id found in step 4, and enter the data in Table2
    Last edited by gutlez; 11-26-2013 at 02:00 PM.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

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.