Red Gate forums :: View topic - Compare two tables in same Database
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Data Compare 10
SQL Data Compare 10 forum

Compare two tables in same Database

Search in SQL Data Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
rfiscus



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 3:00 pm    Post subject: Compare two tables in same Database Reply with quote

I am just trying to compare two tables in the same database for data differences, it seems there isn't a way to map one table to another, just like tables in different databases. Is this possible, any suggestions.

This is an example:

If I have table `dbo.PortsBaselineTable` and `dbo.PortsTable`

Each have the exact same column names:

[Date],[Plant],[ComputerName],[Protocol],[LocalAddress],[LocalPort],[RemoteAddress],[RemotePort],[State],[ProcessName],[Baseline]

I want to be able to query the resulting differences. The dbo.PortsBaselineTable will have about 6000 rows and the Baseline Column will have the number '1', the dbo.PortsTable will continually add data and the Baseline Column will have '0' for all rows. I want to pick two dates one from each table and compare all the rows that are different based on the two particular dates and then grouped by computer name.

Is this even feasible? I would mind having the data in the same table with just the baseline flag changing as I add more data.

Here is a snapshot of the result set:


Date Plant ComputerName Protocol LocalAddress LocalPort RemoteAddress RemotePort State ProcessName Baseline
2014-01-30 07:52:58 plantname COMPUTERNAME TCP 10.10.40.100 49157 192.168.0.2 64807 ESTABLISHED lsass 1
Back to top
View user's profile Send private message
rfiscus



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 3:23 pm    Post subject: Eliminate Date fields Reply with quote

I figured it out. I have removed the Date field from the Comparison key mapping but it still seems like it is comparing these two fields and giving a resultant set on this. Also is there a way to see the SQL that it is generating in order to do the compare?
Back to top
View user's profile Send private message
rfiscus



Joined: 04 Feb 2014
Posts: 3

PostPosted: Tue Feb 04, 2014 3:27 pm    Post subject: Solved Reply with quote

Found it under Object Mapping (Partial)
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6677

PostPosted: Wed Feb 05, 2014 11:29 am    Post subject: Reply with quote

Hello,

Thanks for following up. You can compare data in the same database by placing the same database on the left and right sides or the project screen. Then you can use the object mapping tab to map the two tables together and then map the columns. As long as the datatypes of the columns are similar enough to be compared, you can compare different tables and columns this way.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group