Get data from WHERE NOT EXISTS

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

SQL Server Development (2000)

Get data from WHERE NOT EXISTS

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-10 : 15:33:15

I am trying to insert data like this:


INSERT INTO tblHUDdata (CaseNumber) 
VALUES ('011-000000')
WHERE NOT EXISTS
(SELECT CaseNumber FROM tblHUDdata WHERE CaseNumber = '011-000000')

Is there a way to run it so if that case number already exists in the table, it won't insert it? WHERE NOT EXISTS? But really my question is, is there a way to get that data? Thanks!

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-10 : 15:42:27

if NOT EXISTS (SELECT CaseNumber FROM tblHUDdata WHERE CaseNumber = '011-000000')
begin
INSERT INTO tblHUDdata (CaseNumber)
VALUES ('011-000000')
end

Go with the flow & have fun! Else fight the flow

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-10 : 15:46:11

Yeah, that is perfect. Thanks!

Is there a way to view if it was inserted or not?

Brenda

If it weren't for you guys, where would I be?

mprolli
Starting Member

24 Posts

Posted - 2004-12-10 : 15:48:38

declare @id nvarchar(40)
set @id = '011-000000'

If ( Select Count(*)
from tblHUDData
where caseNumber = @id) = 1
Begin
print 'ID '+@Id+' already Exists'
End
If ( Select Count(*)
from tblHUDData
where caseNumber = @id) = 0
begin
insert into tblHUDdata
(caseNumber)
select @id
end

Let us rise up and be thankful, for if we didn't learn a lot today, at least we learned a little, and if we didn't learn a little, at least we didn't get sick, and if we got sick, at least we didn't die; so, let us all be thankful.
--Buddha

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-12 : 22:47:22

if this is in QA, check the messages tab, and you'll see the number of rows inserted

or you can always select the "inserted" data to check if they're there?

--------------------
keeping it simple...

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-13 : 12:39:59

OK, thanks for all the help. One more thing though. This is what I am trying to do:

declare @id varchar(15)
declare @counter int
declare @length int

set @counter = 0
set @id = (select CaseNumber from tblHudData WHERE NOT EXISTS (SELECT CaseNumber FROM tblForeverHUDdata))
set @length = (select count(casenumber) from tblHUDdata)

while @counter < @length
begin
print @id

set @counter = @counter + 1
set @id = (select CaseNumber from tblHudData WHERE NOT EXISTS (SELECT CaseNumber FROM tblForeverHUDdata ))
end

I don't know how to get one case number at a time though. I can't do TOP 1. Is there any way to loop though the data. Every case has a PK of a casenumber. They do go in order, but are very random numbers. Any ideas?

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-13 : 13:17:01

and why can't you do top 1??
the way you're doing it gets you the last CaseNumber that fits the condition.

what are you trying to do??

Go with the flow & have fun! Else fight the flow

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-12-13 : 13:21:11

I know I can do TOP 1, but how do I get to the next case number after that?

I am trying to display the data that we already have. I need to just have EXISTS instead of NOT EXISTS. But how can I loop through all the cases?

Brenda

If it weren't for you guys, where would I be?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-13 : 13:40:14

to get the "next one" you need to order them by something and use that something as an iterator. if there is no such column use a cursor.

what are you trying to do that needs a loop?? very little stuff needs a loop....

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources