Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlslick
Yak Posting Veteran
83 Posts |
 Posted - 2009-05-31 : 19:51:47
|
| Hello to all SQL Gurus out there!I need assistance building a query. Here is the scenario:- There are 2 databases: DB1 (Test DB) and DB2 (Production DB)- Each database has the exact same tables (i.e. ITEMS)- ITEMS has several fields: ITEM_NUMBER (PK), ITEM_DESCRIPTION, etc...The ITEMS table in each database is essentially the same. The only difference is the ITEMS table in the Production database (DB2) gets updated on a regular basis and the ITEMS table in the Test database (DB1) does not. I need a query that will compare the contents of ITEMS in DB1 with ITEMS in DB2 and determine what has changed in ITEMS in DB2 and make the necessary updates in ITEMS in DB1. I need to compare field for field, except for the Primary Key of course. If this gets too complicated just give me the SELECT statement to extract what has changed in DB2 and then I will build the UPDATE query with the values from the extraction.If this is too simple please excuse my ignorance. Thank you in advanced for your assistance!! |
|
|
satish.ppro
Starting Member
2 Posts |
Posted - 2009-06-01 : 07:09:36
|
| Hi You have to use trigger for that production table. Yes Update Trigger. If there is any changes done in this table then do the suitable changes. Thanks & Regards Satish Pai B |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2009-06-01 : 07:59:48
|
| Sounds like a job for replication/log file shipping/DTS. You pay a lot of money for your DB engine - use what you pay for and don't reinvent the wheel. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2009-06-01 : 15:12:09
|
| Thank you for all your responses guys! However, I am not a DBA so I don't have access to the SQL Server databases and tables. Also, the tables have been exported to Microsoft Access tables. One of the tasks was to identify the new records in DB2 that are not yet in DB1. I simply did a LEFT JOIN query to accomplish this. Now I have to identify what has changed in DB2 that has not yet changed in DB1. Can we accomplish this with an SQL query? Keep note that I just need to compare 2 tables at a time. i.e. ITEMS in DB1 with ITEMS in DB2.Thank you!! |
|
|
|
sqlslick
Yak Posting Veteran
83 Posts |
Posted - 2009-06-02 : 11:33:46
|
| Hey guys, I was able to figure out what I need and I thought I would share it with those that are interested. It turned out to be simple NATURAL JOIN query. I guess the task sounded a lot more complicated then what it actually was.Here is the SQL to compare only 1 field in the ITEMS table. Here, I have assumed that the tables are in the same database for simplicity. If you wanted to compare more or all fields you would simply just add OR conditions in the WHERE clause:SELECT ITEMS_2.ITEM_NO, ITEMS_2.ITEM_REF_NO, ITEMS_2.DESCR FROM ITEMS_1, ITEMS_2 WHERE ITEMS_1.ITEM_NO = ITEMS_2.ITEM_NO AND ITEMS_1.DESCR <> ITEMS_2.DESCR ; |
|
|
|
|
|
|
Query to identify changes