Insert, select and compile instructions

Question

How to insert an entry from table A into table B based on the conditions in table C.

Table a:

ID Name DateFrom DateTo 1 Alex 22-7-2015 26-7-2015 2 Alice 21-8-2015 25-8-2015 

Table C:

 ID Quarter DateFrom DateTo 1 Quater 1 1-7-2015 31-7-2015 2 Quater 2 1-8-2015 31-8-2015 

If the entries from table A are between the date range in table C. It will be inserted into the new table B.

Foreclosure - Table B

 insert into redemption(staffID,staffName,department,pointsAccumulated,referrerID) select referrerStaffID,referrerName,referrerDepartment,SUM(points),activeDirectoryID FROM referral WHERE NOT EXISTS (select * from redemption1 where referrerID=activeDirectoryID) group by activeDirectoryID; 

Answer 1

try it

 Insert into tableB(Id,name,datefrom,dateto) select t1.Id,t1.name,t1.datefrom,t1.dateto from tableA as t1 inner join tableC as t2 on t1.id=t2.id where t1.datefrom between t2.datefrom and t2.dateto or t1.dateto between t2.datefrom and t2.dateto 

Answer 2

I would use a correlated subquery with the WHERE NOT EXISTS.. construct WHERE NOT EXISTS..

 insert into redemption( staffID, staffName, department, pointsAccumulated, referrerID ) select referrerStaffID, referrerName, referrerDepartment, SUM(points), activeDirectoryID FROM referral r WHERE NOT EXISTS ( select 1 from redemption1 r1 where r1.referrerID=r1.activeDirectoryID and (r.datefrom between r1.datefrom and r1.dateto or r.dateto between r1.datefrom and r1.dateto) ) group by r.referrerStaffID,r.referrerName,r.referrerDepartment,r.activeDirectoryID; 

Answer 3

You can use the following query

 insert into tableB(id,name,datefrom,dateto) select A.id,A.name,A.datefrom,A.dateto from tableA A join tableC C on A.id=C.id where TO_DATE(A.datefrom,'DD-mm-yyyy') between TO_DATE(C.datefrom,'DD-mm-yyyy') and TO_DATE(c.dateto,'DD-mm-yyyy') and TO_DATE(A.dateto,'DD-mm-yyyy') between TO_DATE(C.datefrom,'DD-mm-yyyy') and TO_DATE(c.dateto,'DD-mm-yyyy') 

Please check the answer here SQL Fiddle

Answer 4

I think everyone else is already responding to this, but I would just like to add, if the schema for table A and table B is the same, you can also do something like:

 Insert into tableB Select * from tableA Where.... 

It does not repeat where the proposal is, as it has already answered.