Sql Server Database Comparison

Posted : admin On 5/2/2019
Sql Server Database Comparison Rating: 4,1/5 1319 reviews

Your posting was a little vague. I think that lead the the last poster to give you an answer which would work for NEW values in table A that aren't in table B but it ignores any unique constraints that might be on your table B ID column.

When I check diffs b/t 2 tables I usually use the EXCEPT clause

Except returns any distinct values from the left query that are not also found on the right query. Hindi fonts.

so you could use

SELECT * FROM tableA

EXCEPT

SELECT * FROM tableB

and

SELECT * FROM tableB

EXCEPT

SELECT * FROM tableA

to see all the differences.

-------------------------------------

Your description is a little vague though.

It looks like you just want to update tableB with any differences that were in tableA

I'm unsure of your identity column specs so I'll act like you have none and that your ID column is = b/t both dbs.

/* Get all the rows in A that are not in B (or differ from B) and put them into #diffs */

SELECT * INTO #diffs

FROM [db1].dbo.tableA

EXCEPT

SELECT * FROM [db2].dbo.tableB

SELECT * FROM #diffs -- Print records that are in A but not in B or differ from B

/* Update B and set Source and Number columns to the values in A */

UPDATE tB

SET Source = DIFF.source

Secret game 3 1994. • Create a Free Website - Easy, Amazing and Unlimited! • • Default Web Site Page • Two Player Games - 2 Player Games for Two Players • Fighting Games Online - fighting games unblocked, fighting games 2 player, fighting games free, fighting games pc, fighting games for kids, fighting games download, fighting games for android • The Secret Visualization The Secret Book The Secret Movie • The Law of Attraction| The Secret Movie, The Secret Book, Secret DVD, The Power Book by Rhonda Byrne • The Absolute Secret - Long Lost Books Revealing The True Secrets To Life, Success, And Happiness.

, number = DIFF.number

FROM

Killer bean unleashed online. db2.dbo.tableB TB

INNER JOIN

#diffs DIFF

ON DIFF.ID = TB.ID

SELECT @@ROWCOUNT -- select out the # of rows affected

/* Usually after this you'd do an INSERT for rows that were in A but not in B but your spec didn't call for it */

/* Then possibly a delete for rows that left A and need to leave B */

ps sorry for posting untested adhoc sql. I'm hoping it compiles but the semantics look good. Between Garadin's quick post and mine you should be able to pull this off with no problem. Let us know if you need more detailed info.

-Michael Abair