Compare column data between databases
-
I need to compare a column in database A with another column in database B. is there an good/easy way to do this? Via a query and store to a table or file?
Any help would be much appreciated!
-
This might help... I am comparing between Development and Production as an example...
SELECT DEV.IdentityField AS 'Dev IdentityField',
PROD.IdentityField AS 'Prod IdentityField',
-- other fields
FROM TableA DEV
INNER JOIN ServerName.dbname.dbo.TableA PROD
ON( DEV.IdentityField = PROD.IdentityField)
WHERE DEV.ColumnA = 'Something'
AND PROD.ColumnA = 'Something'
ORDER BY DEV.IdentityField
I wasn't born stupid - I had to study.
-
RedGate Software also sells an inexpensive tool to help do this. Their Data Compare product.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Thanks Steve and Farrell. Farrell, can I use the following to get the records in development that are not in production:
SELECT DEV.IdentityField AS 'Dev IdentityField',
PROD.IdentityField AS 'Prod IdentityField',
-- other fields
FROM TableA DEV
INNER JOIN ServerName.dbname.dbo.TableA PROD
ON( DEV.IdentityField = PROD.IdentityField)
WHERE DEV.ColumnA != PROD.ColumnA
ORDER BY DEV.IdentityField
-
I also forgot to mention that the two databases are on separate database servers. Can this still be accomplished using the code above?
-
Yes. If you will note, I used ServerName and dbname (database name) and TableA and called one DEV and one PROD. You can specify whatever names you need. This is simply a template.
Name the server, name the database, and name the table of interest and it should work if they share an Identity Field or Primary Key(s).
I wasn't born stupid - I had to study.
-
Got the following error:
Server: Msg 7202, Level 11, State 2, Line 2
Could not find server 'icedata' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
-
Cory, the examples above assume that you have already linked the servers.
If your databases sit on different servers and are SQL server one way you can do:
- in EM go to Security-->Linked servers --right click to create a linked server.
If your databases sit on 2 different db server systems, still can do....need more to explore more options.
-
Farrell, I would like to return data that is in table B but not in table A. Is it possible to modify your query above to do this? If so, how?
-
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = 'ServerName')
BEGIN
EXEC sp_addlinkedserver 'ServerName', N'SQL Server'
EXEC sp_addlinkedsrvlogin 'ServerName'
END
-- Show DEV not in PROD
SELECT DEV.IdentityField AS 'Dev IdentityField'
, PROD.IdentityField AS 'Prod IdentityField'
-- other fields
FROM TableA DEV
FULL OUTER JOIN ServerName.dbname.dbo.TableA PROD
ON DEV.IdentityField = PROD.IdentityField
AND DEV.ColumnA <> PROD.ColumnA
ORDER BY DEV.IdentityField
-- Show PROD not in DEV
SELECT PROD.IdentityField AS 'Prod IdentityField'
,DEV.IdentityField AS 'Dev IdentityField'
-- other fields
FROM ServerName.dbname.dbo.TableA PROD
FULL OUTER JOIN TableA DEV
ON PROD.IdentityField = DEV.IdentityField
AND PROD.ColumnA <> DEV.ColumnA
ORDER BY PROD.IdentityField
Andy
-
Thanks David but that did not seem to work. The two tables that I am working with do not seem have the same identity values....I guess that was due to testing or something so the first record in each table from the two databases did not start with the same identity value. So I tried using another field that is not an identity field but it is the field that I am trying to evaluate to see if both tables have the same values (or different). Is there a way to modify the query above to allow me to do this?
-
I think that you should post your query and highlight the field(s) that you want to compare.
Andy
-
Agreed. And maybe post some output or desired output...
I wasn't born stupid - I had to study.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply