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
81 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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

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

Author Comment

by:Member_2_7967487
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Provide the Inventory table schema please.
0
 

Author Comment

by:Member_2_7967487
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Great.
Cheers mate.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

11 Experts available now in Live!

Get 1:1 Help Now