logic for update or insert new record

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)

logic for update or insert new record

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-12-14 : 10:56:00

I am trying to insert a row into a table if the input variable doesn't already exist in the table, and if it does exist, I want to do an update of the associated records within the row that correspond to the pre-existing variable. The below code works when it is not within the BEGIN...ELSE...END code, but when it is blocked together, it does add a new record, but it also creates duplicates. ANy suggestions?

IF @@rOWcOUNT != 0
BEGIN
Insert into perfor$
(Thedate, Ticker,
Shares)
values (@thedate, @ticker,
@shares)
END
ELSE
IF @@RowCount = 0
BEGIN
Insert into perfor$
(Thedate, Ticker,
Shares)
values (@thedate, @ticker,
@shares)
END

Cheers,
Dirwin

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-14 : 11:14:14

Y do u Insert in both cases ? Don't u have to Update in the first condition ?

May be this will help

if exists (select * from perfor$ where primaryKeyField = @P1)
Update perfor$ set ...... where primaryKeyField = @P1
Else
Insert into perfor$ (....) values (....)

Kristen
Test

22859 Posts

Posted - 2005-12-14 : 11:14:22

You've got two choices I reckon (assuming @thedate is UNIQUE within the [perfor$] table):


IF NOT EXISTS (SELECT * FROM [perfor$] WHERE Thedate = @thedate)
BEGIN
    INSERT INTO [perfor$] ...
END
ELSE 
BEGIN
    UPDATE [perfor$]
    SET ....
    WHERE Thedate = @thedate
END


UPDATE [perfor$]
SET ....
WHERE Thedate = @thedate

IF @@ROWCOUNT = 0 -- No update happened
BEGIN
    INSERT INTO [perfor$] ...
END

Kristen

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-12-14 : 11:37:37

Thanks guys. I ended up using the update then @@RowCount as it was the most similar to my previous code.

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources