Comparing the values of two columns before insert

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 2008 Forums

Transact-SQL (2008)

Comparing the values of two columns before insert

Author

Topic

Vanpire
Starting Member

7 Posts

Posted - 2013-04-16 : 20:06:06

Probably an easy question but I've got a mind blank this morning.

I want to insert values based off if they exist in 2 columns


insert
WHERE	
g.name NOT in (select distinct ([name]) from mdm.TargetTable)

That checks one column, but I need to check if the Combination of name AND Code exist.

Any help would be awesome.

Vanpire
Starting Member

7 Posts

Posted - 2013-04-16 : 21:28:48

I've also just tried the following:


WHERE
CONCAT(t1.name,t2.Season_Code) exists in (select t.ShortSKU_KidsFullSKU + t.Season from mdm.target t)

AND


WHERE
t1.name + t2.Season_Code exists in (select t.name + t.Season from mdm.target t)

Neither of which parse

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-04-16 : 22:51:45

use either EXISTS or IN, not both.
For me IN reads better.

Vanpire
Starting Member

7 Posts

Posted - 2013-04-16 : 23:02:09

Thanks for the advice - The final solution was to not use two tables before the "IN" or "EXISTS"

Final code looked like this if anybody is interested - very easy once I got in the right head space haha


INSERT INTO [stg].[CostPriceTemplate_Leaf]
			([ImportType],[ImportStatus_ID],[Batch_ID],[BatchTag],[ErrorCode],[ShortSKU_KidsFullSKU],[Season])
SELECT	distinct	0 as ImportType, 
					0 as ImportStatus_ID, 
					NULL as Batch_ID, 
					--@CostPriceBatchTag as BatchTag,
					0 as ErrorCode, 
					g.name as [ShortSKU_KidsFullSKU],
					g.Season_Code as [Season]

FROM	mdm.gll g
--Check to see if SKU and Season combo already exist in CPT				
WHERE
g.name + g.Season_Code not in (select cpt.ShortSKU_KidsFullSKU + cpt.Season from mdm.CostPriceTemplate cpt)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-17 : 01:01:39

WHERE
g.name + g.Season_Code not in (select cpt.ShortSKU_KidsFullSKU + cpt.Season from mdm.CostPriceTemplate cpt)

can be better written as

WHERE NOT EXISTS(select 1 from mdm.CostPriceTemplate cpt WHERE cpt.ShortSKU_KidsFullSKU =g.name AND cpt.Season = g.Season_Code)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources