Adding record to table if doesnt exit

Last Post 14 Jul 2008 09:33 PM by GoodFella3993. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
GoodFella3993
New Member
New Member

--
13 Jul 2008 03:38 PM
I have a table:

SourceCode ___ Coutry
AAA1 _______ USA
AAA1 _______ MEX
BBB2 _______ USA
BBB2 _______ MEX
CCC3 _______ USA


I need to find a way to go through this table and make sure that for every SourceCode that has a country of "USA" should also have a country of "MEX"



SourceCode "CCC3" only has a country of "USA" so it would need a new record of:

CCC3 ______ MEX

Any idea of how to do this?

Thanks,
Marc
GoodFella3993
New Member
New Member

--
13 Jul 2008 04:03 PM
I think a cursor my do it ... trying to figure out some sort of logic .......
SwePeso
New Member
New Member

--
14 Jul 2008 01:03 AM
DECLARE @Sample TABLE (SourceCode VARCHAR(4), Country VARCHAR(3))

INSERT @Sample
SELECT 'AAA1', 'USA' UNION ALL
SELECT 'AAA1', 'MEX' UNION ALL
SELECT 'BBB2', 'USA' UNION ALL
SELECT 'BBB2', 'MEX' UNION ALL
SELECT 'CCC3', 'USA'

SELECT *
FROM @Sample

INSERT @Sample
(
SourceCode,
Country
)
SELECT SourceCode,
'MAX'
FROM @Sample
GROUP BY SourceCode
HAVING MAX(CASE WHEN Country = 'USA' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Country = 'MEX' THEN 1 ELSE 0 END) = 0

SELECT *
FROM @Sample
GoodFella3993
New Member
New Member

--
14 Jul 2008 09:33 PM
Great! That worked.

Thank You.
SwePeso
New Member
New Member

--
15 Jul 2008 01:14 AM
Thank you for your feedback.
You did notice the typo? The insert statement should be 'MEX' instead of 'MAX'.
You are not authorized to post a reply.

Acceptable Use Policy