1

I have table1, table2 and table3, all of them have a primary serial key id (or think of any auto increment type). table3 has two foreign keys(table1 id and table2 id).

The questions is, I have 2 databases(db_a and db_b) that have the same schema (tables 1,2,3 with same relations). Now how can I insert data from db_b into db_a in a way that I can keep track of the correct relations between table3 and the table1 and table2.

EDIT

This is not a one time thing, it will reoccur aperiodically.

New data is inserted into db_a on daily basis, however data inserts on db_b can happen with different regularity. The issue is how to keep track of the auto generated keys for table1 and table2 when inserting table3 data into db_a

db schema

  • Which Postgres version ? Before inserting, make sure there are no duplicated data between db_a and db_b related to 3 tables. You might use pg_dump to export data from db_b, then import to db_a, then set 3 sequences of those tables. – Luan Huynh Apr 12 '18 at 13:55
  • @LuanHuynh thanks for the suggestion. I am using Postgres 9.5, however I am more interest in the design or strategy to perform this rather than how to specifically do it in postgres(I might have to use another DBMS). Also, there are no duplicates in the sense that all the new data (db_b) is for a different company, and so are identified with auto incriment keys. Please let me know if you think this is not good practice – AnarKi Apr 12 '18 at 14:08
  • 2
    Please edit your question to provide required info and define how to merge your data exactly. – Erwin Brandstetter Apr 12 '18 at 14:26
  • 1
    @ErwinBrandstetter I just did – AnarKi Apr 12 '18 at 15:08
  • 1
    If you can redesign with UUIDs instead of serial for the primary keys, just do that. – Daniel Vérité Apr 12 '18 at 17:56
1

Just follow this... steps

These are the tables for database1

    create table if not exists table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

There are the tables for database2

    create table if not exists 2table1 (
      id int(11) auto_increment,
      data1 varchar(50),
      primary key(id)
    );
    create table if not exists 2table2 (
      id int(11) auto_increment,
      data2 varchar(50),
      primary key(id)
    );
    create table if not exists 2table3 (
      id int(11) auto_increment,
      t1_id int(11),
      t2_id int(11),
      data3 varchar(50),
      primary key(id)
    );

Here we have temp data for database1

    insert into database1.table1 (data1)
        values('table_1_data_1'),
        ('table_1_data_2'),
        ('table_1_data_3');

    insert into database1.table2 (data2)
        values('table_2_data_1'),
        ('table_2_data_2'),
        ('table_2_data_3');

    insert into database1.table3 (data3,t1_id,t2_id)
        values('table_3_data_1',1,1),
        ('table_3_data_2',1,2),
        ('table_3_data_3',2,1),
        ('table_3_data_4',2,2),
        ('table_3_data_5',2,3);

temp data for database2

    insert into database2.2table1 (data1)
        values('2table_1_data_1'),
        ('2table_1_data_2'),
        ('2table_1_data_3');

    insert into database2.2table2 (data2)
        values('2table_2_data_1'),
        ('2table_2_data_2'),
        ('2table_2_data_3');

Now come to the point

Here we are copying data from database1s table1 to database2s 2table1

and

from database1s table2 to database2s 2table2

    insert into database2.2table1 (data1)
        select data1 from database1.table1;
    insert into database2.2table2 (data2)
        select data2 from database1.table2;

Now we are going to insert data into database2s 2table3

    insert into database2.2table3 (t1_id,t2_id,data3) select
      (select 2t1.id from database2.2table1 2t1 where 2t1.data1 =t1.data1 ),
      (select 2t2.id from database2.2table2 2t2 where 2t2.data2 =t2.data2 ),
      t3.data3
    from database1.table3 t3
    left join database1.table1 as t1 on t1.id = t3.t1_id
    left join database1.table2 as t2 on t2.id = t3.t2_id;

I hope it helps...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.