3

I need to compare the two table data with in one database.match the data using some columns form table.

Stored this extra rows data into another table called "relationaldata". while I am searched ,found some solutin.

But it's not working to me http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

can any one help how to do this. How compare two table data with in one database using redgate(Tool)?

user123
  • 820
  • 3
  • 14
  • 34
  • give some sample tables and queries, something you have worked on... – kevinm Jul 19 '13 at 10:01
  • 1)http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx 2)SELECT col1, col2, col3 FROM (SELECT * FROM tableA UNION ALL select * from tableB) data GROUP BY col1, col2, col3 HAVING count(*)!=2 – user123 Jul 19 '13 at 10:09
  • When i tried this it showing both data rows but i don't need that i want only compare data save this extra rows into new table select * from tableA minus select * from tableB – user123 Jul 19 '13 at 10:12
  • Can Any one help me please this is urgent for me and can any one suggest any tools for comapare – user123 Jul 19 '13 at 10:35

2 Answers2

4

Red Gate SQL Data Compare lets you map together two tables in the same database, provided the columns are compatible datatypes. You just put the same database in the source and target, then go to the Object Mapping tab, unmap the two tables, and map them together.

Data Compare used to use UNION ALL, but it was filling up tempdb, which is what will happen if the table has a high row count. It does all the "joins" on local hard disk now using a data cache.

Wonko
  • 331
  • 1
  • 5
  • Thank you very much for reply do you have any tutorial or video for this how to do? y i am asking because i don't have any idea on this tool just before i came to know about this – user123 Jul 19 '13 at 10:38
1

I think you can use Except clause in sql server 

INSERT INTO tableC
(
   Col1
 , col2
 , col3
)

select Col1,col2,col3from tableA 
Except 
select  Col1,col2,col3 from tableB

Please refer for more information

http://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/

Hope this helps

Thangamani Palanisamy
  • 5,152
  • 4
  • 32
  • 39