insert / update - 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 2005 Forums

Transact-SQL (2005)

insert / update

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-01 : 02:38:47

Hi,
I need to check the memberName in my tbl,if name alredy there,i have to update it and if not insert it.ok.waht iam doing is like please see below

select @cnt = count from tbl where memberName=@membername

if (@cnt=0)
//Insert operation
else
update operation.

My doubts here are
1.Count is the best way to do this insert / Update Operation or wat are the best way to do this.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-01 : 02:42:30

select @cnt = count from tbl where memberName=@membername

insert into tablename ()
select ,,,, where @cnt = 0

update tablename
set columns
where @cnt <> 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 03:59:29

or simply do


...
update tablename
set columns
where membername=@name

if @@rowcount =0
 --insert code

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-01 : 04:43:54

Thanks for the response.

visakh16,
update tbl
set Fname='Peacok'
where Fname='Peacok'

if @@rowcount = 0
insert into t1(Fname)values('Peacok')

i did this way,values go on adding it to the tbl whenever i exeute it.

This is not working perfectly...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 04:54:09

quote:
Originally posted by haroon2k9

Thanks for the response.

visakh16,
update tbl
set Fname='Peacok'
where Fname='Peacok'

if @@rowcount = 0
insert into t1(Fname)values('Peacok')

i did this way,values go on adding it to the tbl whenever i exeute it.

This is not working perfectly...

can i see your used query?

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-01 : 05:06:56

yeah.your query was correct.working ok.please tell me which way is
(Rowcount or Count)Best one to follow.

1.
select @cnt = count from tbl where memberName=@membername

if (@cnt=0)
//Insert operation
else
update operation.

2.update tablename
set columns
where membername=@name

if @@rowcount =0
--insert code

mymatrix
Starting Member

24 Posts

Posted - 2010-02-01 : 07:57:23

using @@rowcount is better than @cnt.

In case of @cnt, you are calculating and executing one extra query every time for getting the total count.
and then storing it in a variable and using it to decide insertion or updation.

In case of @@rowcount, SQL server updates this global variable on every insert/update/delete. So better use the light weight system variable.

thnks
Gaurav

Even my blood group says be -ve to all the negatives.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-01 : 08:35:46

How about:


-- Step 1
IF NOT EXISTS (SELECT 1 FROM tbl WHERE memberName = @memberName) BEGIN
    <INSERT OP>
END
ELSE BEGIN
    <UPDATE OP>
END

-- Step 2
UPDATE tableName SET
    <COLUMNS>
WHERE
    memberName = @name

IF @@ROWCOUNT = 0 BEGIN
    <INSERT CODE>
END

Remember that you either have to store the result of @@ROWCOUNT into a variable after the UPDATE or reference it IMMEDIATELY in an IF statement.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-02-01 : 23:39:01

Thanks for all of u.very excellent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources