Solved

Compare data between two databases

Posted on 2016-09-12
16
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
16 Comments
 
LVL 50

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 66

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 50

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 50

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 50

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
 
LVL 50

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 50

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 50

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 50

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

717 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