If Exists Retrieve Value Help - SQL Server Forums

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

Transact-SQL (2000)

If Exists Retrieve Value Help

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2006-08-01 : 11:01:51

What I need to do:1. If exists in table 1, then retrieve the ID from table 1
2. Insert new record with table1 ID into table 2
3. if doesn't exist in table 1, then just insert new record into table 2
What I have so far:
If EXISTS (SELECT CaseID FROM Cases
WHERE ccNumber=@ccNumber )
Insert INTO Compromised
( CaseID, ccNumber,...)
Values
(*caseID, @ccNumber,...)

ELSE

INSERT INTO Compromised
( ccNumber,....
)
VALUES
(
@ccNumber, ...
)

Where I'm stuck:
I don't know how to get the CaseID when it exists, so I can insert it
into the new table. Can anyone help me? Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-01 : 11:15:19

insert Compromised ( CaseID, ccNumber,...)
select CaseID, ccNumber, ...
from Cases
where ccNumber=@ccNumber

if @@rowcount = 0
insert Compromised (ccNumber,...)
select @ccNumber, ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ann
Posting Yak Master

220 Posts

Posted - 2006-08-03 : 14:34:58

I tried the example you provided but I get the following error:

The name 'caseID' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-03 : 15:13:09

ann,

r u using the exactly same code or using the "Values" keyword and brackets ?

Srinika

ann
Posting Yak Master

220 Posts

Posted - 2006-08-03 : 15:23:12

I'm using Values, brackets and INTO

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-03 : 16:48:48

ann,
That is the reason

the code given by nr doesn't have "Values" keyword

check the syntax for "Insert" in Books OnLine.

Quick Info.
Use values when u have fixed values as constants. if u get data from a select query, and insert, u can't use "Values" keyword.

Syntax is
Insert Into DestinationTbl (<Fields list>)
Select <Fields & constants> from SourceTbl

Values used as follows :

Insert Into DestinationTbl (Field1, Field2....)
Values (Data1, Data2,....)

Srinika

ann
Posting Yak Master

220 Posts

Posted - 2006-08-03 : 17:06:16

I didn't know that - will that query also allow me to pass in parameters? I am using that query in a stored procedure. What I came up with is something like this which works (I didn't know how else to do it):
DECLARE @var1 int

SELECT @var1 = CaseID
FROM Case
WHERE ccNumber=@ccNUM

if @@rowcount > 0

INSERT INTO Compromised .....

else
.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources