IF NOT EXISTS Insert Into Select

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

Transact-SQL (2005)

IF NOT EXISTS Insert Into Select

Author

Topic

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 06:10:43

Here's my query:


INSERT INTO UserCategories (ID, IsUserAssignedCategory, CategoryID)
 SELECT DISTINCT @UserID, 0, CategoryID FROM KeyWords WHERE '%' + @KeyData + '%' LIKE '%' + KeyWord + '%'

I would like to check a condition, that if the combination
@UserID, 0, CategoryID does not exists only then I need to do a Insert against UserCategories. How do I write a NOT EXISTS statement?
DISTINCT would give me distinct CategoryID, but the UserCategories table may already have the said combination.

Thanks
Peeyush

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-05-28 : 06:14:02

Try This

Select @count=count(*) from table where condition......

if( @count>0)
Begin
Print 'Condition matched'
End

Else
Begin
print 'Condition not matched'
End

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-28 : 07:09:23

IF NOT EXISTS(SELECT * FROM UserCategories WHERE ID= @userid AND IsUserAssignedCategory = 0 AND CategoryID EXISTS (SELECT * FROM CategoryID = CategoryID AND '%' + @KeyData + '%' LIKE '%' + KeyWord + '%'))

BEGIN
INSERT INTO UserCategories (ID, IsUserAssignedCategory, CategoryID)
SELECT DISTINCT @UserID, 0, CategoryID FROM KeyWords WHERE '%' + @KeyData + '%' LIKE '%' + KeyWord + '%'
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 07:42:11

Senthil and bklr, both your suggestions only deal with one record.
What if there are multiple matches from KeyWords table?

This is Peeyush should write his code

INSERT	UserCategories
	(
		ID,
		IsUserAssignedCategory,
		CategoryID
	)
SELECT DISTINCT	@UserID,
		0,
		kw.CategoryID
FROM		KeyWords AS kw
LEFT JOIN	UserCategories AS uc ON uc.UserID = @UserID
			AND uc.IsUserAssignedCategory = 0
			AND uc.CategoryID = kw.CategoryID
WHERE		kw.KeyWord LIKE '%' + @KeyData + '%'
		AND uc.UserID IS NULL

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

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 09:15:15

Thanks Peso,
I havent run the query yet, but this confuses me,
the last line
AND uc.UserID IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:17:40

It checks that the current record fetched from KetWords does not exists in the UserCategories table.
When no match using LEFT JOIN, you can check for NULL presence.

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

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 09:44:16

The query seems not to work :(
CategoryID is NULL even if WHERE condition matches.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 09:57:51

And CategoryID is populated?

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

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 10:01:18

Yep

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 10:57:32

Same relpy as before

-- Prepare sample data
DECLARE	@UserCategories TABLE
	(
		UserID INT,
		IsUserAssignedCategory TINYINT,
		CategoryID INT,
		Keyword VARCHAR(20)
	)

INSERT	@UserCategories
	(
		UserID,
		IsUserAssignedCategory,
		CategoryID
	)
SELECT	1, 0, 1 UNION ALL
SELECT	1, 1, 2 UNION ALL
SELECT	2, 0, 3

DECLARE	@Keywords TABLE
	(
		CategoryID INT,
		Keyword VARCHAR(20)
	)

INSERT	@Keywords
SELECT	2, 'SQLTeam Peso' UNION ALL	-- Added for User #1
SELECT	2, 'SQLTeam Forum' UNION ALL
SELECT	1, 'SQLTeam Peso' UNION ALL	-- Not added for User #1 since a category with #1 already exists
SELECT	1, 'SQLTeam Forum'

-- Initialize user supplied parameters
DECLARE	@UserID INT,
	@KeyData VARCHAR(20)

-- Give it a go for User #1
SELECT	@UserID = 1,
	@KeyData = 'Peso'

INSERT	@UserCategories
	(
		UserID,
		IsUserAssignedCategory,
		CategoryID,
		Keyword
	)
SELECT DISTINCT	@UserID,
		0,
		kw.CategoryID,
		kw.Keyword
FROM		@Keywords AS kw
LEFT JOIN	@UserCategories AS uc ON uc.UserID = @UserID
			AND uc.IsUserAssignedCategory = 0
			AND uc.CategoryID = kw.CategoryID
WHERE		kw.KeyWord LIKE '%' + @KeyData + '%'
		AND uc.UserID IS NULL

SELECT	*
FROM	@UserCategories

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

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 11:30:58

SELECT DISTINCT @UserID, 0, UC.CategoryID FROM KeyWords KW
LEFT JOIN UserCategories AS UC ON UC.ID = @UserID
AND UC.IsUserAssignedCategory = 0
AND UC.CategoryID = KW.CategoryID
WHERE '%' + @KeyData + '%' LIKE '%' + KW.KeyWord + '%'
AND UC.ID IS NULL

SELECT DISTINCT @UserID, 0, CategoryID FROM KeyWords KW
WHERE '%' + @KeyData + '%' LIKE '%' + KW.KeyWord + '%'

Unfortunately the first query doesn't work.
I have mailed you, could you please check your mail.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 11:45:19

quote:
Originally posted by pyu.agrawal

SELECT DISTINCT @UserID, 0, KWUC.CategoryID FROM KeyWords KW
LEFT JOIN UserCategories AS UC ON UC.ID = @UserID
AND UC.IsUserAssignedCategory = 0
AND UC.CategoryID = KW.CategoryID
WHERE '%' + @KeyData + '%' LIKE '%' + KW.KeyWord + '%'
AND UC.ID IS NULL

SELECT DISTINCT @UserID, 0, CategoryID FROM KeyWords KW
WHERE '%' + @KeyData + '%' LIKE '%' + KW.KeyWord + '%'

Unfortunately the first query doesn't work.
I have mailed you, could you please check your mail.

Thanks

you're taking CategoryID from UserCategories and then looking for ones not existing in it, thats why the null value. modify like above and try

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-28 : 13:44:10

Why don't you copy and paste the suggestion we make, instead of continue with your own suggestion which doesn't work (that's the reason you ask for help)?

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

pyu.agrawal
Starting Member

29 Posts

Posted - 2009-05-28 : 13:52:39

Thanks visakh16, that solved my problem.
Thanks Peso, for the query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-28 : 14:02:05

welcome...in future try to use exact suggestion given or post the full query if you're using something different

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources