May 5, 2009 at 3:22 pm
I haven't had much luck in searching the forums or Google for that matter. My goal is to compare two tables that are incredibly similar already. One is an import table and the other is working table for the system to use. The working table will be truncated at night and the import table copied over. The problem lies in the fact that I have to maintain a third table that would have two additional columns. One would be for add/remove/update/unchanged and the other would be a datetime stamp of when I ran the query. We have to maintain the historical information and the new compare tool built into 2008 won't track the actual data, just that a change was made and which row was changed. I'm absolutely lousy with joins right now. I'm still learning and I would be happy to read the information myself if one of you would be so kind as to point me in the right direction. The output required would be similar to...
List which records are identical?
List which records would be dropped?
List which records would be added?
List which records would be updated?
The two tables in question reside on a MS SQL Server Enterpise Edition 8 server. I'm using MS SQL Server Mgmnt Studio as my primary application. The two comparison tables are incredibly small. One has 341 rows and the other import is about 347. There's only three columns as well; areacode,st,timezone. Any help would be greatly appreciated.
May 6, 2009 at 3:16 am
hi i think this may help you ,
SELECT t1.*, 'Added' FROM table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
union
SELECT t1.*,'Changed'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
except
SELECT t2.*,'Changed'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
union
SELECT t2.*, 'Deleted'
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
union
SELECT t1.*,'UnChanged'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
INTERSECT
SELECT t2.*,'UnChanged'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
try it out ..................
May 6, 2009 at 10:14 am
Thank you, Grasshopper. It's a great start and very appreciated. Sadly only half of it works. I've contacted the lead DBA to find out why the EXCEPT and INTERSECT are not working. I'll let everyone know what I find out. I tried it on the development box and two different production servers and got the exact same error message so I know it isn't the query. It's got to be something with our SQL configuration.
May 6, 2009 at 11:06 am
Well "Edition 8" is SQL Server version 2000, which is probably the problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2009 at 12:04 am
HI
I tested the query using sqlserver2005 it works fine .
can you please let me know the issue with you .
May 7, 2009 at 7:13 am
RBarry Young noticed it. My server is on SQL Server 2000 even though I'm using the tools from SQL Server 2008 Express to access it. Apparently those commands aren't available on an older server. I don't want to turn my desktop into a 2008 Express server unless there is no other way. I've already got too much overhead as it is and only 1Gb of RAM available.
May 7, 2009 at 10:30 am
OK, this modification of msreddyn's code should work on SQL 2000. Note that you will have to fill in some column names here:
SELECT t1.*, 'Added'
FROM table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
union ALL
SELECT t1.*,'Changed'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
WHERE NOT EXISTS(
SELECT *
FROM table1 t11
INNER JOIN table2 t22 ON t11.ID = t22.ID
AND t11.col1 = t22.col1
AND t11.col2 = t22.col2
AND ...)
union ALL
SELECT t2.*, 'Deleted'
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
union ALL
SELECT t1.*,'UnChanged'
FROM table1 t1
INNER JOIN table2 t2 ON t1.ID = t2.ID
AND t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 9, 2009 at 11:56 pm
excellent script for 2005. Thanks for the post.
Steve
May 10, 2009 at 11:11 am
Glad I could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 10, 2009 at 2:42 pm
If you need to do something like this with any frequency - I would suggest that you invest in the great tools from our sponsors. SQL Compare and SQL Data Compare do exactly what you are looking for - and even build the scripts to synchronize between the two.
They have a trial version available - and I use the tools regularly myself. They have paid for themselves several times over already.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 11, 2009 at 8:03 am
Thank you very much. It works beautifully.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply