assume the following case:
source table: ST
columns: username, address
ST table has 100 records, with 100 different user names, and 100 different addresses
target table 1: Users
columns: ID, username, addressID
target table 2: Address
columns: ID, address
* for each record in the source table (username, address) I want to create 2 records:
** one record in the Address table: ID and address (from ST)
** one record in the Users table: ID, username (from ST), and the ID to the record (which was just created) in Address table that holds the user's adress
I *don't* want to manage the IDs of the Users and Address tables.
I have used tMap , tMySQLInput and tMySQLOutput in my job. I also auto_increment for both Users.ID and Address.ID, but the problem I can't make Users.addressID to have the value of Address.ID, cause I don't know what is the ID that was generated by mysql.
If possible I prefer not to use Mysql "official" foreign keys.
Any hint ?
-- Yonatan
May be a solution for you.
It's how I do (with postgres).
_ 1 : You need to describe your two ID as serial (you need to create a sequence at table creation).
_ 2 : Fill the output tables with an unique tMap (in the same transaction)
_ 3 : Copy "nextval('yourSchema.users_id_users_seq'::regclass)" (<== it's an example) from definition column of ID_users row (inside tMap - schema editor) and clear the column (after copy of course)
_ 4 : Go back to job design, click on advanced settings of output table (users) component
_ 5 : Click on the green cross to add a column
_ 6 : Paste your copy in expression sql column, choose replace in column position, copy the id name in reference column and paste it in the column name to replace "colname" (keep the quotes)
the result : "id_users" (col1) "nextval('yourSchema.users_id_users_seq'::regclass)" (col2) replace (col3) id_users (col4)
_ 7 : Do the same thing for your second table (adress)
the result : "id_adress" (col1) "nextval('yourSchema.adress_id_adress_seq'::regclass)" (col2) replace (col3) id_adress (col4)
_ 8 : At last, go back to advanced settings of users table and add this new line under the first one :
"addressID" (col1) "currval('yourSchema.adress_id_adress_seq'::regclass)" (col2) replace (col3) addressID (col4)
You have synchronyzed your id.
resume :
advanced settings users table
"id_users" (col1) "nextval('yourSchema.users_id_users_seq'::regclass)" (col2) replace (col3) id_users (col4)
"addressID" (col1) "currval('yourSchema.adress_id_adress_seq'::regclass)" (col2) replace (col3) addressID (col4)
advanced settings adress table
"id_adress" (col1) "nextval('yourSchema.adress_id_adress_seq'::regclass)" (col2) replace (col3) id_adress (col4)
Hello CCBD,
Thank you a lot for your explanation about how to proceed to synchronize the ids PK-FK. I do exactly the same as you propose here (also postgres) but i still have a problem.
Either i don't use tpostgresqlconnection and i have
ERROR: currval of sequence "address_addressID_seq" is not yet defined in this session
or I use the connection component at the beginning and tpostgresqlcommit at the end (without commit no data written) and in the child table the FK column for all rows is the last value of the sequence. I mean 10 rows were inserted in "address" and the adress_id in "users" table is 10 for all rows (i paused uniqueness constraint to understand the error).
What am i doing wrong?
Thank you
Hi again,
Found the solution myself, in any case thanks to all.
For those who are interested the problem was following: you have to validate autocommit on dbconnection component and in advanced settings of tDBoutput you have to put commit to 1 row (was 1000 by default) + batch size option should also be 1 instead of 1000 by default
