How to compare two table

Question

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

---

Ajit

Answer 1

Hello Risu,

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

Thank you

Answer 2

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

Answer 3

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.

Answer 4

See your other post with teh same question: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2556c3e0-cf86-43bd-8dc0-c9ab5bdf750c/how-to-use-linq-query-to-compare-two-table?forum=sqlsmoanddmo

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

How you will store result of these query into separate table.

---

Ajit

Answer 6

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.