locked
How to compare two table RRS feed

  • Question

  • I have two table having identical schema. I want to compare two these two table to store different records in separate table.

    Ajit

    Friday, December 22, 2017 8:35 AM

Answers

  • Hello Ajit,

    Use:

    Insert into <your table name>(<col1>,<col2>,<col3>)

    select s1.col1,s1.col2,s1.col3 from student1 s1

    left join student2 s2 on s1.studentid=s2.studentid

    where s2.studentid is null

    Thank you.


    • Edited by Curendra Friday, December 22, 2017 9:15 AM
    • Marked as answer by Risu Raj Friday, December 22, 2017 9:20 AM
    Friday, December 22, 2017 9:13 AM

All replies

  • Hello Risu,

    Can you please elaborate what actually you want? It is not much clear.

    Thank you

    Friday, December 22, 2017 8:44 AM

  • Can you please elaborate what actually you want? It is not much clear.

    Suppose there are two table student1 and student2. Both table have same column name and same datatype.

    I want to store those records in separate table  which is present in student1 table but not in student2 table. 


    Ajit

    Friday, December 22, 2017 8:53 AM
  • Hello Risu,

    You may try something like following:

    select s1.* from student1 s1

    left join student2 s2 on s1.studentid=s2.studentid

    where s2.studentid is null

    This gives you the records from student1 which are not in student2.

    For records in student2 which are not in student1, you can use:

    select s2.* from student2 s2

    left join student1 s1 on s1.studentid=s2.studentid

    where s1.studentid is null

    If you need such records from both student1 and student2 which are not present in the other, then you can UNION the above two queries.

    Hope this helps.

    Thank you.

    Friday, December 22, 2017 9:03 AM
  • How you will store result of these query into separate table.

    Ajit


    • Edited by Risu Raj Friday, December 22, 2017 9:09 AM
    • Marked as answer by Risu Raj Friday, December 22, 2017 9:20 AM
    • Unmarked as answer by Risu Raj Friday, December 22, 2017 9:21 AM
    Friday, December 22, 2017 9:09 AM
  • Hello Ajit,

    Use:

    Insert into <your table name>(<col1>,<col2>,<col3>)

    select s1.col1,s1.col2,s1.col3 from student1 s1

    left join student2 s2 on s1.studentid=s2.studentid

    where s2.studentid is null

    Thank you.


    • Edited by Curendra Friday, December 22, 2017 9:15 AM
    • Marked as answer by Risu Raj Friday, December 22, 2017 9:20 AM
    Friday, December 22, 2017 9:13 AM