Comparing UPC codes - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing UPC codes

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2011-09-14 : 00:54:20

I am trying to merge several databases, but I'm not sure how to compare in SQL. The logic is simple. For every row entered, I need to see if the current UPC already exists in the new database.
If it does not exist: it should enter the row. If it does exist: it should compare price. If the price is lower, it should enter the row.

Can anyone start me off in the right direction?

SergioM
Posting Yak Master

170 Posts

Posted - 2011-09-14 : 16:12:18

I expect it to look something like this. Can anyone tell me if they would do it differently or would add more to it?

loop					-- Begin Loop
IF (UPC = current_UPC_Variable)		-- If the variable being imported is already equal to one Already within the Database
  THEN WHERE price is better, UPDATE by x criteria ';
  ELSE enter row;  
end loop;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-15 : 00:54:50

you dont need loop. you can do set based like


INSERT INTO NewDB.dbo.Table
SELECT columns
FROM oldDB.dbo.Table t
WHERE NOT EXISTS(SELECT 1 FROM NewDB.dbo.Table WHERE UPC=t.UPC)

UPDATE n
SET n.Price=o.Price
FROM oldDB.dbo.Table o
INNER JOIN newDB.dbo.Table n
ON o.UPC = n.UPC
WHERE n.Price < o.Price

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2011-09-19 : 13:04:14

Ahh, this is perfect. Much cleaner than my code. Thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 13:09:53

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-09-19 : 14:40:54

Why not use MERGE? After all, it was designed to deal with these scenarios.

MERGE	newDB.dbo.Table1 AS tgt
USING	oldDB.dbo.Table2 AS src ON src.UPD = tgt.UPC
WHEN	MATCHED AND tgt.Price < src.Price
		THEN	UPDATE
			SET	tgt.Price = src.Price
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					UPC,
					Price
				)
			VALUES	(
					src.UPC,
					src.Price
				);

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 14:47:18

I thought not to suggest it as I was not sure whether OP was on SQL 2008 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2011-09-21 : 10:44:27

Interesting. I'm testing this out on SQL Server 2005, but the production machine is SQL Server 2008. I wasn't expecting issues with portability, but this is too streamlined to pass up. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources