How to use If not exists

Question

Post reply

How to use If not exists

chandrasekaran.ganapathy

SSCommitted

Points: 1738
More actions
March 19, 2010 at 4:00 am

#156799

I have two tables Table1 and Table2.
Table1:
BugId, EngineerId,EngineerName, BugStatus
Table2:
EngineerId, EngineerName, No of Resolved Bugs.
I just wanted to take out the EngineerId and EngineerName from Table1 and put it Table2.
Whenever I insert BugId, EngineerId,EngineerName,and BugStatus into Table1, I just wanted look at the table2 and if engineerid is not there I have to insert into Table2 or leave it.
Can you just give me some idea how to use if not exists or any other scenario?
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

chandrasekaran.ganapathy (3/19/2010)
Can you just give me some idea how to use if not exists or any other scenario?

Try thisdeclare @t table ( EngineerId int )

insert into @t

select 1

union select 3

union

select 5

select * from @T

declare @t2 table ( EngineerId int )

insert into @t2

select 5 union select 3

select * from @T2

insert into @t2

select * from @t

where EngineerId not in ( select EngineerId from @t2)

select * from @T2

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 2

This is ok if the table1 does not have the repetition of EngineerId. But Table1 has the repetition of EngineerId. I've tried your code. It says Primary Key violation.

Can you give me any other solution.

Please mail me "Chandrasekaran.ganapathy@patni.com"

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

chandrasekaran.ganapathy (3/19/2010)
This is ok if the table1 does not have the repetition of EngineerId. But Table1 has the repetition of EngineerId. I've tried your code. It says Primary Key violation.
Can you give me any other solution.
Please mail me "Chandrasekaran.ganapathy@patni.com"

You can use the DISTINCT Clause to remove the duplicates..

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 4

chandrasekaran.ganapathy

SSCommitted

Points: 1738

March 19, 2010 at 4:54 am

#1136588

Cool it is working... Thanks for the gr8 help.

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 5

Insert into Table2(EngineerId,EngineerName)

select Distinct(EngineerId),EngineerName from Table1

where EngineerId not in (select (EngineerId) from Table2)

Why it is not retrieving EngineerName?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

chandrasekaran.ganapathy (3/19/2010)
Why it is not retrieving EngineerName?

it should . can you post some sample data where this query is failing ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 7

BugId EngineerId EngineerName State

10233 64867 Chandrasekaran Resolved

10235 64865 Srikanth Addagalla Assigned

........

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

there is no duplicate values and secondly is this from table A or table B ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 9

This is from Table1.

I have just given some sample values, Duplications are allowed in this table. Because one engineer can raise any no. of bugs. only thing is bug id is uhique.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

chandrasekaran.ganapathy (3/19/2010)
select Distinct(EngineerId),EngineerName from Table1
where EngineerId not in (select (EngineerId) from Table2)

this query will exclude all the records/engineerid which are present in table2.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 11

chandrasekaran.ganapathy

SSCommitted

Points: 1738

March 19, 2010 at 5:59 am

#1136625

Will it retrieve engineername or not?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

NO, it will give only those engineerid and engineername which are not present in table2

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 13

Does the below query return any NULL values

select (EngineerId) from Table2

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

chandrasekaran.ganapathy SSCommitted Points: 1738 More actions · Answer 14

chandrasekaran.ganapathy

SSCommitted

Points: 1738

March 19, 2010 at 6:18 am

#1136641

No it is not bringing null.