mysqldatabaseselectinsert-query

Pupulate table with data in mysql

I have two databases

  1. db1

  2. db2

db1 has table controller below the description of controller table.

+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| id                    | int(11)      | NO   | PRI | NULL    |       |
| interface_name        | varchar(255) | YES  |     | NULL    |       |
| store_number          | int(11)      | YES  |     | NULL    |       |
| file_seq_no           | int(11)      | YES  |     | NULL    |       |
| packet_seq_no         | int(11)      | YES  |     | NULL    |       |
| last_trans_start_date | date         | YES  |     | NULL    |       |
| last_trans_end_date   | date         | YES  |     | NULL    |       |
| last_extract_datetime | datetime     | YES  |     | NULL    |       |
| is_enabled            | char(1)      | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+

and same table description and additional column(date_opened) with different name available in database db2 with name store table.

My job is to write a query based on below logic.

Point 1 : Fetch all store_number, date_opened from db2.store.

Point 2 : Fetch all store_number from db1.controller table into list.

Point 3 : Filter new store_number fetched from db2.store not present in db1.controller.

For each of these new store_number

insert default initial values for new stores, file_seq_number and packet_seq_number will be set as zero.

last_transaction_start_date , last_transaction_end_date , last_extract_timestampwill be store creation date ie db2.store.date_opened

enabled will be set as 1

Main query would be 

Insert into db1.controller table values( store_number, 0, 0,$date_opened,$date_opened,
$date_opened,1);

Can any one please help me to complete this sql query.

Thanks in advance

Solution

  • This something what i did

    INSERT INTO db1.controller (interface_name,store_number,file_seq_no,packet_seq_no,last_trans_start_date,last_trans_end_date,last_extract_datetime,is_enabled) SELECT 'test',s.store_number,0,0,s.date_opened,s.date_opened,s.date_opened,'1' FROM db2.store s where s.store_number not in (select store_number from db1.controller);