• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

compare two table t1,t2 in sql server

i have to compare two table t1,t2 in sqlserver
t1 and t2 have same no. of rows and columns.
i have to open a cursor on t1 and compare with table t2 and if the values in any of the row is different.i want to insert the total row in to another table t3.
can any one help me
give me an example considering 2 rows and 2 columns
0
vijay11
Asked:
vijay11
  • 4
  • 3
  • 2
1 Solution
 
Patrick MatthewsCommented:
Hello vijay11,

You can almost certainly do this without using a cursor.  Avoid using cursors if at all possible!  :)

If you could provide a brief example, I or another Expert should be able to help you on this.

Regards,

Patrick
0
 
vijay11Author Commented:
my requirement is with cursor
i have similar tables with same columns and rows.but the data in one table has been changed.so we loaded the 2 days old backup and we are comparing each and every row.and if the data in any row is different .i want the entire row to be inserted to a third table.my requirement is with cursor.can some one help me
0
 
Nathan RileyFounder/CTOCommented:
Somthing like this?  Just need to change your fields and add more case statements for additional columns.
insert into table3
select case when case when t1.field <> t2.field
from table1 t1
inner join table2 t2 on t1.id = t2.id

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
vijay11Author Commented:
i want using a cursor.this is my requirement
0
 
Nathan RileyFounder/CTOCommented:
oh ok sorry here you go:
declare @YourField1 varchar(255)
declare @YourField2 varchar(255)
 
declare C1 cursor for
select t1.yourfield,
t2.yourfield
from table1 t1
inner join table2 t2 on t1.id = t2.id
 
OPEN C1
FETCH NEXT FROM C1
INTO 	@YourField1, @YourField2
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
if @YourField1 <> @YourField2
begin
insert into Table3
values(@Yourfield1,@YourField2)
end
 
FETCH NEXT FROM C1
INTO 	@YourField1, @yourField2
 
END
CLOSE C1
DEALLOCATE C1

Open in new window

0
 
Patrick MatthewsCommented:
vijay11,

With respect, why the requirement to use a cursor?  A cursor is always going to be slower than more
conventional SQL.

Is this perchance for an academic assignment?

Regards,

Patrick
0
 
vijay11Author Commented:
one of our clients always want the scripts using  cursor
0
 
vijay11Author Commented:
i have 23 columns in a table1 and table2 .with same colums and rows.i have to compare 2 tables and depending upon primary_id(i.e we have to check if primary_ids in 2 tables are same if thy are same then we have to compare the every row.and if any of the value is different then we have to insert the total row in to a third table(can do without using a cursor)

note:with out using a cursor
0
 
Patrick MatthewsCommented:
Seems straightforward enough...


SELECT t1.*, t2.*
INTO NewTable
FROM Table1 t1 INNER JOIN
      Table2 t2 ON t1.ID = t2.ID
WHERE t1.col1 <> t2.col1 OR
      t1.col2 <> t2.col2 OR
      t1.col3 <> t2.col3 OR
      ...
      t1.colN <> t2.colN


Replace column and table names as needed.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now