One Star

copy database

Hello,
I have 2 databases. They are le same tables, same name and structure. But in the second database for each table, I add 2 columns (ID autoinc, DB_NAME)
I would like to create a job wich take the all table of the first DB (automatically) and insert data in the same table of my second DB and add in the column DB_NAME, a constant (for example : "DATABASE_1")
But I don't know how I can do :/
Can you help me please ???
Sorry for my english :/
13 REPLIES

Re: copy database

in a tMap output table, along with all the original columns from your source db, add an extra output column (1) (very bottom of tmap interface) and put "DATABASE_1" in the input cell for this new column (2).
One Star

Re: copy database

Yes, I know, I already use this method but I would like a method which run all tables of my database
Community Manager

Re: copy database

Hello
ut I would like a method which run all tables of my database

You can use a tMysqlTableList to list all the tables and iterate each one.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: copy database

Thank you for your help.
I have not again test your solution bu I would like an information on the picture2.
In the label "Query", What can I write as instruction because according to the tables, the requests aren't the same.
And last question, in the tmap component, no mapping ? it's automatically ? (look my picture)

Thank you.
Community Manager

Re: copy database

Hello Flavien
If the tables don't the same structure(schema), we can't use this solution. as we must define the schema manually before running the job.
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: copy database

:/
BD1, Table1 have same structure than DB2, Table1
But BD1, Table1 haven't same structure than DB1, Table2
DB2 is a copy of DB1 with 2 more columns (ID, BDD_NAME).
In DB1, there are more 70 tables :/ So I would like to use a method wich run all tables af DB1 and insert in table correspondig and adding a constant for the column BDD_NAME.
Have you an idea ?
Thank you
One Star

Re: copy database

I have an idea :
1/ I list tables in my DB1,
2/ and after I list column in my DB1.
3/ I use this result for my request "SELECT" in my DB1
4/ And I insert datas in my DB2.
But I have a problem with step 3 and step 4
Step 3 (tMMSqlInput2) :
The result of listing column is an array ? It's necessary to explode this result for use it in my request ?
Step 4 (tMSSqlRow_1) :
How can I do to insert data in my DB2 ?

Thank you so much
One Star

Re: copy database

Can you help me ?
One Star

Re: copy database

Does Talend MDM provides us with databse synchronization?
Moderator

Re: copy database

Hi Jyoti_kumari,
Please open a new forum under Talend Open Studio for MDM and Talend Enterprise MDM » Non technical discussions for your topic.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seventeen Stars

Re: copy database

In Talend Exchange exists a component which is designed for the task to synchronize database.
In case of MySQL use this:
http://www.talendforge.org/exchange/index.php?eid=554&product=tos&action=view&nav=1,1,1
This component does not care about slightly different schemas. Only the columns which exists in the source and target will be transferred. It is not necessary to know the schema and this component can transfer the data in ETL mode: means over the network with different database connections.
One Star

Re: copy database

I want to transfer data from an oracle (source) database to a postgresql (destination) database. I created a table on the destination postgresql database to mirror the source table. However, the schemas are different, for example the oracle table uses the db type "number" for an integer column, while the destination posgresql table uses the db type "Integer' (Int4). Talend creates schemas with the type "bigdecimal" for the oracle db type "number" and it creates the type "int/integer" for the postgresql db type "Int4". I've tried to manually define the schema so the types match, but I am not so sure I'm doing it correctly  as it gives an error: ERROR: column "<comlumnname> " of relation "<tablename>" does not existException in component tPostgresqlOutput_1. But the column exists with the correct name just that I tried changing the type and db type to match the source, if I dont do that I get an error on the tPostgresqlOutput_1 component that the input row# type does not match
Moderator

Re: copy database

Hi Pru,
Could you please show us the screenshot of schema setting in tPostgresqlOutput?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.