none
how to ignore duplicate records already exists in table & insert only new records RRS feed

  • Question

  • Insert into table1 (
    col1,col2,col3,col4.... coln)
    select 
    col1,col2,col3,col4.... coln from table2

    col1,col2,col3,col4 is a unique non clustered index 

    col2 is a datetime datatype

    there are duplicate col1 to col4 on table1, table2

    how to ignore the duplicate into table1 insert only new records /

    i need somethign like 

    insert into table1 (select table2 - duplicate col1 to col4)

    Also how to equate table1.col2 = table2.col2  - col2 is datetime field

    Friday, November 8, 2019 4:31 PM

Answers

  • Insert into table1 (col1,col2,col3,col4.... coln)
    select col1,col2,col3,col4.... coln 
    from table2 a
    where not exists (select 1 from table1 b
                      where a.col1 = b.col1
                      and a.col2 = b.col2
                      and a.col3 = b.col3
                      and a.col4 = b.col4)

    if you want to remove duplicates in table2 then you can just use a select into a temp tamp and group by key columns. Let's hear from you if you have any issues first. Good luck.

    Saturday, November 9, 2019 9:15 AM

All replies

  • For the first question, maybe try a query like this too:

     

    insert into Table1 ( col1, col2, col3, col4, col5, ... )

    select col1, col2, col3, col4, col5, ... from Table2

    where not exists (select col1, col2, col3, col4 intersect select col1, col2, col3, col4 from Table1 )

     

    Friday, November 8, 2019 8:16 PM
  • Insert into table1 (col1,col2,col3,col4.... coln)
    select col1,col2,col3,col4.... coln 
    from table2 a
    where not exists (select 1 from table1 b
                      where a.col1 = b.col1
                      and a.col2 = b.col2
                      and a.col3 = b.col3
                      and a.col4 = b.col4)

    if you want to remove duplicates in table2 then you can just use a select into a temp tamp and group by key columns. Let's hear from you if you have any issues first. Good luck.

    Saturday, November 9, 2019 9:15 AM