SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comparing the values of two columns before insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vanpire
Starting Member

7 Posts

Posted - 04/16/2013 :  20:06:06  Show Profile  Reply with Quote
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 - 04/16/2013 :  21:28:48  Show Profile  Reply with Quote
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
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/16/2013 :  22:51:45  Show Profile  Reply with Quote
use either EXISTS or IN, not both.
For me IN reads better.
Go to Top of Page

Vanpire
Starting Member

7 Posts

Posted - 04/16/2013 :  23:02:09  Show Profile  Reply with Quote
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)

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/17/2013 :  01:01:39  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000