insert into a table from another table when a new record is inserted

0
votes

I have 2 tables, I want to insert all records from the first table into the second one if the records do not exist. If a new row is added to the first table, it must be inserted into the seccond one.

I found this query INSERT INTO Table2 SELECT * FROM Table1 WHERE NOT EXISTS (SELECT * FROM Table2) but if a new row is added to table1, the row is not inserted into table2.

PS: table1 and table2 have the same fields and contain thousands of records

2
pythonsql
Are you looking for a way to do this in Python? Because this looks like a job for a trigger in MySQL/PostgreSQL/... – Willem Van Onsem
I am using python to connect to databases, the problem is that I don't know the query that will do this. I found this solution: 'insert into Table2 select * from table1 where not exists (select * from Table2)', but if a new record is added to the table1, it is not inserted in table2. !!! – kh.b
What SQL database are you using? Afaik triggers are not standard SQL so it depends on your database system whether it can be done. – Willem Van Onsem
I am using access database – kh.b
look into Triggers. – Mujtaba Kably

2 Answers

0
votes

You could do it all in SQL - if you can run, for example, a batch every 2 minutes ...

-- with these two tables and their contents ...
DROP TABLE IF EXISTS tableA;
CREATE TABLE IF NOT EXISTS tableA(id,name,dob) AS (
          SELECT 42,'Arthur Dent',DATE '1957-04-22'
UNION ALL SELECT 43,'Ford Prefect',DATE '1900-08-01'
UNION ALL SELECT 44,'Tricia McMillan',DATE '1959-03-07'
UNION ALL SELECT 45,'Zaphod Beeblebrox',DATE '1900-02-01'
);
ALTER TABLE tableA ADD CONSTRAINT pk_A PRIMARY KEY(id);

DROP TABLE IF EXISTS tableB;
CREATE TABLE IF NOT EXISTS tableB(id,name,dob) AS (
          SELECT 43,'Ford Prefect',DATE '1900-08-01'
UNION ALL SELECT 44,'Tricia McMillan',DATE '1959-03-07'
UNION ALL SELECT 45,'Zaphod Beeblebrox',DATE '1900-02-01'
);
ALTER TABLE tableB ADD CONSTRAINT pk_B PRIMARY KEY(id);

-- .. this MERGE statement will ad the row with id=42 to table B ..

MERGE
INTO tableB t
USING tableA s
 ON s.id   = t.id
WHEN NOT MATCHED THEN INSERT (
  id
, name
, dob
) VALUES (
  s.id
, s.name
, s.dob
);
0
votes

So it's an Access database.

Tough luck. They have no triggers.

Well then. I suppose you know how to insert a row into Access using Python, so I won't go through that.

I'll just build a scenario just after having inserted a row.

CREATE TABLE tableA (
  id   INTEGER
, name VARCHAR(20)
, dob  DATE
, PRIMARY KEY  (id)
)
;
INSERT INTO tableA(id,name,dob) VALUES(42,'Arthur Dent','1957-04-22');
INSERT INTO tableA(id,name,dob) VALUES(43,'Ford Prefect','1900-08-01');
INSERT INTO tableA(id,name,dob) VALUES(44,'Tricia McMillan','1959-03-07');
INSERT INTO tableA(id,name,dob) VALUES(45,'Zaphod Beeblebrox','1900-02-01');

CREATE TABLE tableB (
  id   INTEGER
, name VARCHAR(20)
, dob  DATE
, PRIMARY KEY  (id)
)
;
INSERT INTO tableB(id,name,dob) VALUES(43,'Ford Prefect','1900-08-01');
INSERT INTO tableB(id,name,dob) VALUES(44,'Tricia McMillan','1959-03-07');
INSERT INTO tableB(id,name,dob) VALUES(45,'Zaphod Beeblebrox','1900-02-01');

Ok. Scenario ready.

Merge ....

MERGE
INTO tableB t
USING tableA s
 ON s.id   = t.id
WHEN NOT MATCHED THEN INSERT (
  id
, name
, dob
) VALUES (
  s.id
, s.name
, s.dob
);

42000:1:-3500:[Microsoft][ODBC Microsoft Access Driver] 
Invalid SQL statement; 
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

So, Merge not supported.

Trying something else:

INSERT INTO tableB
SELECT * FROM tableA
WHERE id <> ALL (SELECT id FROM tableB)
;

1 row inserted

Or:

-- UNDO the previous insert
DELETE FROM tableB WHERE id=42;
1 row deleted
-- retry ...
INSERT INTO tableB
SELECT * FROM tableA
WHERE id NOT IN (SELECT id FROM tableB)
;
1 row inserted

You could run it like here above.

Or, if your insert into Table A, from Python, was:

INSERT INTO tableA(id,name,dob) VALUES(?,?,?);

... and you supplied the values for id, name and dob via host variables, you could continue with:

INSERT INTO tableB 
SELECT * FROM tableA a 
WHERE id=? 
  AND NOT EXISTS(
   SELECT * FROM tableB WHERE id=a.id
  );

You would still have the value 42 in the first host variable, and could just reuse it. It would be faster this way in case of single row inserts.

Should you perform mass inserts, then, I would insert all new rows to table A, then run the INSERT ... WHERE ... NOT IN or the INSERT ... WHERE id <> ALL .... .