Solved

Compare data between two databases

Posted on 2016-09-12
  • Databases
  • Query Syntax
  • MS SQL Server
  • MS SQL Server 2005
  • MS SQL Server 2008
  • +1
16
95 Views
Last Modified: 2016-09-21
Hi Everyone,

Any assistance would be greatly appreciated. BIG THANKS!!!

Delete items that are no longer available in the database:
1) I would like a query to compare the 'Item Number' column from the Items_Remove database against the 'Item Number' column in the Inventory database.   If the item number matches, then delete the entire record from the Inventory database.

Update 'Available Inventory' column in the Inventory database:
2) I would like a query to compare the 'Item Number' column from the Items_Update database against the  'Item Number' column in the Inventory database.  If the item number matches, then update the 'Available Inventory' column value from the Items_Update database to the Inventory database.

Inventory
0
Comment
Question by:Member_2_7967487
  • 8
  • 7
16 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41795959
NOTE: Please run both commands in a test environment before running it in Production to verify if the behavior is the expected one.
1)
DELETE FROM Inventory
WHERE EXISTS (SELECT 1 
        FROM Items_Remove 
        WHERE Items_Remove.[Item Number] = Inventory.[Item Number])

Open in new window

2)
UPDATE Inventory
SET [Available Inventory] = (SELECT [Available Inventory]
        FROM Items_Update
        WHERE Items_Update.[Item Number] = Inventory.[Item Number])

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41795975
DELETE with JOIN example

UPDATE with JOIN example

DELETE Inventory..items
FROM Inventory..items i 
	JOIN Items_Remove..items ir ON i.[Item Number] = ir.[Item Number]

UPDATE u
SET u.[Available Inventory] = i.[Available Inventory]
FROM Items_Update..Updates u
   JOIN Inventory..items i ON u.[Item Number] = i.[Item Number]

Open in new window


Couple of comments:
  • Your question spells out database names and column names, but not table names, so I assumed them based on your image.
  • Having spaces in table or column names is a poor practice as it forces the developer to surround it with square brackets [ ], which is one thing to potenntially forget and throw an error.
  • Note the use of the double dots .., that assumes that all tables are the same as the default schema dbo.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41796022
Good point on the database names, Jim. I didn't realize that before.
So, with that information I just changed my scripts:
USE Inventory
GO
DELETE FROM Items
WHERE EXISTS (SELECT 1 
        FROM Items_Remove..Remove R 
        WHERE R.[Item Number] = Items.[Item Number])

UPDATE Items
SET [Available Inventory] = (SELECT [Available Inventory]
        FROM Items_Update..Update U
        WHERE U.[Item Number] = Items.[Item Number])

Open in new window

0
 

Author Comment

by:Member_2_7967487
ID: 41796444
Victor's scripts work the best. Thank you for your help!

The Delete Items works, but there is an issue with the Update items.  I am attaching the error for your review. Thanks Jim and Victor for your help and responding.

error.jpg
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41797373
It's the table name. I wrote it in singular (Update) but should be plural (Updates). Here's the fix:
USE Inventory
GO
DELETE FROM Items
WHERE EXISTS (SELECT 1 
        FROM Items_Remove..Remove R 
        WHERE R.[Item Number] = Items.[Item Number])

UPDATE Items
SET [Available Inventory] = (SELECT [Available Inventory]
        FROM Items_Update..Updates U
        WHERE U.[Item Number] = Items.[Item Number])

Open in new window

0
 

Author Comment

by:Member_2_7967487
ID: 41798423
Thanks Vitor.

The script works, but it marked all variable as, 'OK', rather the updating the actual 'available inventory' number.  I am attaching the screenshot for your review.

error2.jpg
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41799255
Can you provide the data type from both tables?
0
 

Author Comment

by:Member_2_7967487
ID: 41800872
They are only number type.
I would need to compare the 'item number' on both tables. If the 'item number' matches, then update the 'available inventory' from the  dbo.updates to the odb.items.

This example would compare the 'item number' AF91366 on both tables. There is a matched, and there is 5 'available inventory' left in the dbo.updates table, so update number 5 to the 'available inventory' in the dbo.items table.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41801068
They are only number type.
Well a numeric field wouldn't present an 'OK' value, right? Please confirm the 'Available Inventory' data type in both tables.
0
 

Author Comment

by:Member_2_7967487
ID: 41801471
You are correct.

Available Inventory field prior to running the query as listed below.

Item Updates table --> Numeric only
Inventory table --> Empty or NULL. After running the query, it posted as OK instead of updating to a numeric from the 'updates' table.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41801494
Provide the Inventory table schema please.
0
 

Author Comment

by:Member_2_7967487
ID: 41801720
Hi Vitor,

Below are the Inventory and the update tables schemas for your review. Thanks.


Inventory Table:
Inventory.jpg
Updates Table:
Updates.jpg
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41804366
Ok, this confirms that [Availabe Inventory] in table Items is nvarchar(510) so can't store a number.
What is weird is that is updated to 'OK' instead of '5' so my guess is that you have a trigger in that table that changed the value to OK. Can you please confirm the existence of a trigger on that table?
0
 

Author Comment

by:Member_2_7967487
ID: 41805882
There aren't any trigger on the table. Data are imported from an excel spreadsheet.

I am going to reimport the excel and provide a feedback.
0
 

Author Comment

by:Member_2_7967487
ID: 41807952
Updates:

Victor, I was successfully ran your updates query after I changed the data type in.  Thank you for all your help!!!

Tools-->Options-->Designers-->Table and Database designers-->Uncheck-->Prevent saving changes that required table re-creation.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41808157
Great.
Cheers mate.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now