Select a row from one table and insert into another table

Question

Select rows from table1 that do not exist in table2, and insert them into table2

as

Images

id type name 502 1 summer.gif 

SEOImages

 id idimage ... ... 1000 501 ... ... 

Now I want to select all rows from the Images table whose identifier does not match the idimage SEOImages table and insert these rows into the SEOImages table.

Answer 1

An approach:

 Insert into Table2 select A,B,C,.... from Table1 Where Not Exists (select * from table2 where Your_where_clause) 

Example:

SQLFiddelDemo

 Create table Images(id int, type int, name varchar(20)); Create table SEOImages(id int, idimage int); insert into Images values(502,1,'Summer.gif'); insert into Images values(503,1,'Summer.gif'); insert into Images values(504,1,'Summer.gif'); insert into SEOImages values(1000,501); insert into SEOImages values(1000,502); insert into SEOImages values(1000,503); insert into SEOImages select 1000,id from Images I where not exists (select * from SEOImages where idimage =I.id); 

Answer 2

 INSERT INTO SeoImages (IdImage) SELECT ID FROM Images WHERE ID NOT IN (SELECT IDIMAGE FROM SEOImages) 

Answer 3

Request:

 SELECT * FROM Images WHERE id NOT IN (SELECT idimage FROM SEOImages) 

should output these lines from images that do not have a corresponding identifier in SEOImages, if they are both of the same type.

Alternatively using JOIN:

 SELECT i.* FROM Images i LEFT OUTER JOIN SEOImages s on i.id = s.imageId WHERE s.imageId IS NULL 

Answer 4

 INSERT INTO SEOImages SELECT * FROM Images WHERE NOT EXISTS (SELECT 1 FROM Images t1, SEOImages t2 WHERE t1.id=t2.id) ;