• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

Compare data between two databases

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
Member_2_7967487
Asked:
Member_2_7967487
  • 8
  • 7
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Member_2_7967487Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Member_2_7967487Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you provide the data type from both tables?
0
 
Member_2_7967487Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Member_2_7967487Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Provide the Inventory table schema please.
0
 
Member_2_7967487Author Commented:
Hi Vitor,

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


Inventory Table:
Inventory.jpg
Updates Table:
Updates.jpg
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Member_2_7967487Author Commented:
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
 
Member_2_7967487Author Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Great.
Cheers mate.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now