October 22, 2009 at 7:05 pm
I have a situation need to compare data in two identical tables location of two database on same server and print difference in rows. After that update rows from table A to table B and print number of rows updated. Can anyone help me with sample script in 2005. For example apps table1 on database 1 with Id, source, number compare same table on database2 and return difference after that update rows in table 1 to table2
October 22, 2009 at 7:24 pm
Different databases isn't really an obstacle, you just need to provide the database name as part of the object name. For example, say you have DatabaseA and DatabaseB.
All you need to do is something like:
SELECT * FROM DATABASEA.dbo.Table
EXCEPT
SELECT * FROM DATABASEB.dbo.Table
then likely:
INSERT INTO DATABASEB.dbo.Table
SELECT * FROM DATABASEA.dbo.Table
EXCEPT
SELECT * FROM DATABASEB.dbo.Table
SELECT @@ROWCOUNT
Note: Untested, in the middle of re-installing everything after my windows 7 upgrade =).
October 22, 2009 at 8:22 pm
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.
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
, number = DIFF.number
FROM
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
October 22, 2009 at 8:41 pm
Micheal thanks for your note.
what is SET Source = DIFF.source
, number = DIFF.number in your statement?
Bascially I'm looking for compare table A and table B and update table b if there are any difference in it.
October 22, 2009 at 9:30 pm
DIFF is a table alias to the #diffs table.
I created the #diffs table earlier in the batch here
SELECT * INTO #diffs
FROM [db1].dbo.tableA
EXCEPT
SELECT * FROM [db2].dbo.tableB
------------------
Then I gave it the alias "DIFF" in the statement below.
UPDATE tB
SET Source = DIFF.source
, number = DIFF.number
FROM
db2.dbo.tableB TB
INNER JOIN
#diffs DIFF --<---- This is where I assign the alias DIFF to #diffs
ON DIFF.ID = TB.ID
SELECT @@ROWCOUNT -- select out the # of rows affected
October 23, 2009 at 6:35 pm
Micheal thanks again for clarification! If there is no difference row between table A and table B I want to stop loop there without doing any updates. can you tell me logic to do it along with puting everything in store proc instead of straight update script
October 23, 2009 at 8:35 pm
Thank you Garadin for your quick note.
October 23, 2009 at 8:41 pm
BTW there is no Looping in this script..
CREATE PROCEDURE dbo.procname
as
BEGIN
SET NOCOUNT ON
SELECT * INTO #diffs
FROM [db1].dbo.tableA
EXCEPT
SELECT * FROM [db2].dbo.tableB
IF @@ROWCOUNT = 0
RETURN
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
, number = DIFF.number
FROM
db2.dbo.tableB TB
INNER JOIN
#diffs DIFF
ON DIFF.ID = TB.ID
SELECT @@ROWCOUNT -- select out the # of rows affected
END
GO
October 23, 2009 at 9:34 pm
Thanks Micheal for quick response. Last question what is the statement
number = DIFF.number in the code I belive it is ID field in my table A vs ID field in Diff table ?
October 24, 2009 at 7:56 am
UPDATE tB
SET Source = DIFF.source
, number = DIFF.number
This is part of the update statement and it's actually setting the number in table B equal to the number column in #DIFF. it also is setting the Source column equal to the Source column in #DIFF
The two tables (tableB and #Diff) are joined based on the ID field in the code below that..
FROM
db2.dbo.tableB TB
INNER JOIN
#diffs DIFF
ON DIFF.ID = TB.ID
October 29, 2009 at 3:43 pm
Hi abair34,
Based on ID field which is primary key in the apps how I do I browser all the tables in Database A and Add missing rows in all tables of Database B instead of doing comparision by each table
Thanks
October 29, 2009 at 4:55 pm
vision59 (10/29/2009)
Hi abair34,Based on ID field which is primary key in the apps how I do I browser all the tables in Database A and Add missing rows in all tables of Database B instead of doing comparision by each table
Thanks
Okay, at this point you need to step back and consider what it is you are trying to accomplish. If you want Database B to be an exact copy of Database A - then all you need to do is backup Database A and restore it as Database B (overwriting the existing database).
If, however - there is a set of tables that you want synchronized, there are much better ways than trying to code this yourself. It all depends upon how often and how current it needs to be.
If this is an ad-hoc type of request, but you think it is going to happen again - I would recommend that you look into SQL Data Compare from Redgate.
If this is something that needs to happen on a regular basis - and can afford to be behind by a day, then you can use a backup/restore process.
If this needs to be more current, and automated and available - then lookup Replication, Log Shipping, Mirroring and Snapshot in Books Online.
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
October 29, 2009 at 6:19 pm
this is only one time I need to sync up some table data between database A and database B. Backup and restore will not work in my case. That way I'm struggling to come up a solution. I need to add new rows in database B based on my key field in database A. Intiallly I got solution to sync up table by table with forum guys help now I'm looking for add new rows in database B tables based on primay key all the tables instead going by table vs table process
October 29, 2009 at 6:49 pm
vision59 (10/29/2009)
this is only one time I need to sync up some table data between database A and database B. Backup and restore will not work in my case. That way I'm struggling to come up a solution. I need to add new rows in database B based on my key field in database A. Intiallly I got solution to sync up table by table with forum guys help now I'm looking for add new rows in database B tables based on primay key.
Okay, tell me if I am understanding correctly. You want to insert rows in to DatabaseB.TableB from DatabaseA.TableA where the rows in TableA do not exist in TableB?
Something like:
INSERT INTO DatabaseB.dbo.TableB ({list of columns in TableB})
SELECT {matching list of columns from tableA}
FROM dbo.TableA a
WHERE a.ID NOT IN (SELECT b.ID FROM DatabaseB.dbo.TableB);
Or, using a join you would do the following:
INSERT INTO DatabaseB.dbo.TableB ({list of columns in TableB})
SELECT {matching list of columns from tableA}
FROM dbo.TableA a
LEFT JOIN DatabaseB.dbo.TableB b ON b.ID = a.ID
WHERE b.ID IS NULL;
Run the select portions of the code above to validate that you have selected the correct information. Then run the insert and those rows will be added to the other table.
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
October 29, 2009 at 8:36 pm
Thanks for your suggestion Jeffrey. You are right it works great for table by table process. I want to add new rows based on primary keys in database A to all tables in database B using identity column or foreign key reference how to do it?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply