Can I make this more elegant? - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Can I make this more elegant?

Author

Topic

contec
Starting Member

9 Posts

Posted - 2006-12-06 : 18:24:45

Hi,

I currently have two tables, both without primary keys. I need to check one table for existence of 'duplicate' rows (but only dependant on 3 columns, not all) - if a duplicate row exists, I update, if it doesn't exist, I insert.

Here's my current 'insert' code - it's quite clunky and ugly I think and there MUST be a way to do this better!

INSERT INTO TABLE2(SKU, StoreID, TransactionDate, Qty_OnHand, Qty_Sold, Sales_Amount, Tax)
SELECT
SKU, StoreID, TransactionDate, Qty_OnHand, Qty_Sold, Sales_Amount, Tax
FROM
Table1
WHERE
CAST(SKU AS VARCHAR)+CAST(storeid AS VARCHAR)+CAST(TransactionDate as varchar)
NOT IN (SELECT CAST(SKU AS VARCHAR)+CAST(storeid AS VARCHAR)+CAST(TransactionDate as varchar) FROM Table2)

Can anyone suggest a better method than this very crude way?

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-12-06 : 19:26:13

A bit cleaner.....

INSERT INTO TABLE2(SKU, StoreID, TransactionDate, Qty_OnHand, Qty_Sold, Sales_Amount, Tax)
SELECT  T1.SKU, T1.StoreID, T1.TransactionDate, T1.Qty_OnHand, T1.Qty_Sold, T1.Sales_Amount, T1.Tax
FROM   Table1 TI LEFT JOIN Table2 ON T1.SKU = T2.SKU AND T1.storeid = T2.storeid AND T1.TransactionDate = T2.TransactionDate
WHERE T2.SKU IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-07 : 03:02:49

INSERT INTO	TABLE2
		(
			SKU,
			StoreID,
			TransactionDate,
			Qty_OnHand,
			Qty_Sold,
			Sales_Amount,
			Tax
		)
SELECT		t1.SKU,
		t1.StoreID,
		t1.TransactionDate,
		t1.Qty_OnHand,
		t1.Qty_Sold,
		t1.Sales_Amount,
		t1.Tax
FROM		Table1 t1
WHERE		NOT EXISTS (SELECT * FROM Table2 t2 WHERE t2.SKU = t1.SKU AND t2.StoreID = t1.StoreID AND t2.TransactionDate = t1.TransactionDate)

Peter Larsson
Helsingborg, Sweden

contec
Starting Member

9 Posts

Posted - 2006-12-07 : 13:11:29

Perfect, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources