sql select insertion from table 2 to table 1

Question

I have two tables in my sql database:

Columns in table 1:

entity ID, contact person,  contact ID,  created date

Columns in Table 2:

contact ID   entity ID    modified date   contact person
1             2            10/12/13         MR.A
1             2            11/12/13         MR.B
4             16           17/12/13         MR.C
4             16           19/12/13         MR.D

I want to insert records from table 2 to table 1 where modified date is maximum for the same contact ID.

The output should be like:

Table 1:

entity ID  contact person  contact ID  created date
2           MR.B              1         11/12/13
16          MR.D              4         19/12/13

Please suggest how to write such a SQL query. I have used some queries with max and have but didn't get my answer, guess that's not correctly placed ?

Thanks in advance

Answer 1

It looks like you don't have a primary key on Table2. If you did then this would be much easier as you could use that in the join. However as you don't then this sql does the trick:

INSERT INTO Table1("entity ID", "contact person", "Contact ID", "created date")
SELECT b."entity ID",  b."contact person", b."contact ID",  b."created date"
FROM
(
    SELECT "Contact ID", "entity ID" , MAX("created date")  "created date"                 
    FROM Table2
    GROUP BY "Contact ID", "entity ID" 
) a
JOIN Table2 b ON b."Contact ID" = a."Contact ID" AND
                 b."entity ID" = a."entity ID" AND
                 b."created date" = a."created date"

Sql fiddle can be found here

Answer 2

Well you need to lear it, next time try to get a solution.

INSERT INTO  table_1 (entity_ID, contact_person,  contact_ID,  created_date)
SELECT entity_ID, contact_person, contact_ID, Max(created date)
FROM table_2
Group by entity_ID, contact_person, contact_ID

Answer 3

insert into table1 values (select t2.entityid, t2.contactperson, t2.contactid, (select max (t2.createddate) from table2 t22 where t22.entityid = t2.entityid) from table2 t2 groupby t2.entityid, t2.contactperson, t2.contactid )

Answer 4

Use the below select query to get the data and then insert the data:

select 
    t1.contact_id, t1.ENTITY_ID, t1.MODIFIED_DATE, t1.CONTACT_PERSON 
from 
    test_table t1,
    (select 
         contact_id, max(modified_date) as max_modified_date 
     from 
          test_table 
     group by 
          contact_id) t2 
where 
     t1.contact_id = t2.contact_id and t1.MODIFIED_DATE = t2.max_modified_date;

Answer 5

Try This :

INSERT INTO Table1 (EntityID,ContactPerson,ContactID,CreatedDate)
SELECT 
EntityID,ContactPerson,ContactID,ModifiedDate
FROM
(
Select Row_NUMBER() Over(Partition By ContactId order by ModifiedDate DESC) ROW_NO,
EntityID,ContactPerson,ContactID,ModifiedDate 
from Table2
) AS T WHERE T.ROW_NO = 1