Insert and update in one stored proc (inserting record twice)

Question

Insert and update in one stored proc (inserting record twice)

Patrick.I

Hall of Fame

Points: 3105
More actions
November 24, 2008 at 10:11 pm

#154948

I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:

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

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

Patrick Ige (11/24/2008)
I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:

use this [not tested]

If Exists (Select * From table WHERE a=@a and b=@b)

update table

set

a= @a,

b=@b

where

a=@a and b=@b

GO

Insert into c

a,b

select

@a,@b

from table where

a=@a and b=@b

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Patrick.I Hall of Fame Points: 3105 More actions · Answer 2

Thanks Kraynot but if i insert GO

I would have to declare all the variables

Pls test it.

krayknot (11/24/2008)
Patrick Ige (11/24/2008)
I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:
use this [not tested]
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
where
a=@a and b=@b
GO
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b

krayknot SSCertifiable Points: 7998 More actions · Answer 3

Patrick Ige (11/24/2008)
Thanks Kraynot but if i insert GO
I would have to declare all the variables
Pls test it.
krayknot (11/24/2008)
Patrick Ige (11/24/2008)
I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:
use this [not tested]
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
where
a=@a and b=@b
GO
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b

Then you can remove Go as Go signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Patrick.I Hall of Fame Points: 3105 More actions · Answer 4

What i'm saying is it doesn't work 🙂

You are sending the same code i posted LOL :doze:

krayknot (11/24/2008)
Patrick Ige (11/24/2008)
Thanks Kraynot but if i insert GO
I would have to declare all the variables
Pls test it.
krayknot (11/24/2008)
Patrick Ige (11/24/2008)
I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:
use this [not tested]
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
where
a=@a and b=@b
GO
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Then you can remove Go as Go signals the end of a batch of Transact-SQL statements to the SQL Server utilities.

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 5

Insert into c

a,b

select

@a,@b

from table where

not(a=@a and b=@b)

does that help???

Patrick.I Hall of Fame Points: 3105 More actions · Answer 6

Nope that didn't help either.

I just want to update a row and insert that row into another table.

Lynn Pettis (11/24/2008)
Insert into c
a,b
select
@a,@b
from table where
not(a=@a and b=@b)
does that help???

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 7

Okay, your original post isn't the clearest on what you are trying to accomplish, so here is my suggestion. Read the article I have linked below in my signature block the discusses how best to ask for help that really helps.

Following the guidelines in that article and report your question in this thread. You may be surprised and actually get response that really helps you solve a problem.

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 8

Patrick Ige (11/24/2008)
I have and update and insert statement in a stored proc like so:
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
Insert into c
a,b
select
@a,@b
from table where
a=@a and b=@b
Why am i getting 2 records inserted at the same time to table c.
Any ideas.. Guess i'm blind:blush:

follow the below example, it may helpful to you, it's working fine.

create table #testA

(idint

,namevarchar (10)

)

go

create table #testB

(idint

)

go

insertinto#testA

select1, 'abc'

union all

select2, 'xyz'

go

declare@IDint

set@ID= 2

ifexists (select* from #testA where ID = @ID)

begin

update#testA

setname= 'hai'

whereID= @ID

end

insertinto#testB(id)

select@ID

from#testA

whereID = @ID

go

select*from#testA

go

select*from#testB

go

Patrick.I Hall of Fame Points: 3105 More actions · Answer 9

Thanks Lynn.

I just read that.

I was sure i was clear enough.

I simply have an insert and update statement in a stored proc.

I want to update and then insert the record that was updated into another table as you can see in my first post.

It actually works but the problem is i get the same record update twice.

Thanks

Lynn Pettis (11/24/2008)
Okay, your original post isn't the clearest on what you are trying to accomplish, so here is my suggestion. Read the article I have linked below in my signature block the discusses how best to ask for help that really helps.
Following the guidelines in that article and report your question in this thread. You may be surprised and actually get response that really helps you solve a problem.

winash SSChampion Points: 11446 More actions · Answer 10

Maybe there are two rows in the table to begin with - and your insert statement is just inserting both the records?

Maybe there's a trigger on the table c that's causing an extra row to be inserted?

If you follow Lynn's advice and go through the article listed and provide us some sample data/code we'll be able to help you better...otherwise we'll have to make assumptions on what is causing the issue...

And I have the following questions around the code you posted (this might or might not be relevant to the issue at hand)

-- what exactly is being done here? It looks like you are updating the

-- columns to what value they already have?

If Exists (Select * From table WHERE a=@a and b=@b)

update table

set

a= @a,

b=@b

from table where

a=@a and b=@b

-- looks like incorrect syntax?

Insert into c

a,b

select

@a,@b

-- maybe "table" here has two records where a = @a and b = @b-2??

from table where

a=@a and b=@b

Patrick.I Hall of Fame Points: 3105 More actions · Answer 11

sample data:

select company,code,openedyr,openedmonth from companies where code=@code and openedy=@openedyr and openedmonth=@openedmonth

company code openedyr openedmonth

-------- ----- --------- ------------

microsoft 01 2007 12

sun 02 2008 11

when i do an update e.g

update companies

set

company= @company,

where code=@code and openedy=@openedyr and openedmonth=@openedmonth

-- and then i insert at the sametime

Insert into companyupdate

company

select

@company

from companies where

code=@code and openedy=@openedyr and openedmonth=@openedmont

i don't think i have 2 records.Becos when i update my grid it updates correctly.

I don't have any triggers.

What i have on the insert companyupdate table is a unique ID which is increment.(its just keeps inserting records..)

Any ideas...

winash (11/25/2008)
Maybe there are two rows in the table to begin with - and your insert statement is just inserting both the records?
Maybe there's a trigger on the table c that's causing an extra row to be inserted?
If you follow Lynn's advice and go through the article listed and provide us some sample data/code we'll be able to help you better...otherwise we'll have to make assumptions on what is causing the issue...
And I have the following questions around the code you posted (this might or might not be relevant to the issue at hand)
-- what exactly is being done here? It looks like you are updating the
-- columns to what value they already have?
If Exists (Select * From table WHERE a=@a and b=@b)
update table
set
a= @a,
b=@b
from table where
a=@a and b=@b
-- looks like incorrect syntax?
Insert into c
a,b
select
@a,@b
-- maybe "table" here has two records where a = @a and b = @b-2??
from table where
a=@a and b=@b

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Patrick

What do you expect the following statement to do:

select

@company

from companies where

code=@code and openedy=@openedyr and openedmonth=@openedmont

Without testing it, I reckon it will return one column with the pre-assigned value of @company, and as many rows (with this same value) as are returned from the SELECT i.e.

select COUNT(*)

from companies where

code=@code and openedy=@openedyr and openedmonth=@openedmont

...which appears to be returning two rows.

I'm guessing that you mean something like this:

select @company = company

from companies

...

GROUP BY company

Which for an INSERT...FROM may as well be:

select company from companies where...

Unless, of course, you're planning on using the value assigned to @company later in the batch.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Patrick.I Hall of Fame Points: 3105 More actions · Answer 13

Chris why do you reckon this

select COUNT(*)

from companies where

code=@code and openedy=@openedyr and openedmonth=@openedmont

would return 2 rows?

If i send the paramters 01,2007,12 i do get a single row

If i'm getting you are you saying my insert is wrong?

If yes what should it be?

Chris Morris (11/25/2008)
Patrick
What do you expect the following statement to do:
select
@company
from companies where
code=@code and openedy=@openedyr and openedmonth=@openedmont
Without testing it, I reckon it will return one column with the pre-assigned value of @company, and as many rows (with this same value) as are returned from the SELECT i.e.
select COUNT(*)
from companies where
code=@code and openedy=@openedyr and openedmonth=@openedmont
...which appears to be returning two rows.
I'm guessing that you mean something like this:
select @company = company
from companies
...
GROUP BY company
Which for an INSERT...FROM may as well be:
select company from companies where...
Unless, of course, you're planning on using the value assigned to @company later in the batch.
Cheers
ChrisM

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

Patrick Ige (11/25/2008)
Chris why do you reckon this
select COUNT(*)
from companies where
code=@code and openedy=@openedyr and openedmonth=@openedmont
would return 2 rows?
If i send the paramters 01,2007,12 i do get a single row
If i'm getting you are you saying my insert is wrong?
If yes what should it be?

Hi Patrick

1. The SELECT above will return 1 row, but what's the result of the COUNT(*)? If your INSERT is inserting more than one row, it's because the SELECT component is returning more than one row. COUNT(*) will tell you how many rows would be returned.

2. You're not SELECTing a value from the table, you're returning the value already assigned to the variable @company - because there's no assignment to the variable within the SELECT. When you assign a value to a variable in a select (SELECT @company = company), you will have only one row in the output. But without the assignment, you will have as many rows as are returned by the SELECT alone, each one having the value already assigned to @company.

3. I can't tell why you are using @company in your select. There are good reasons to do so - you might wish to use the value later in the batch. If you aren't sure why you are using @company in the SELECT, then you probably don't want to do it.

4. ALWAYS test INSERT INTO...SELECT... (or UPDATE...FROM...) by testing the SELECT part separately. In your case, you're getting multiple INSERTs because your SELECT is returning more than one row.

5. There's insufficient information to determine what the SELECT part of your INSERT INTO...SELECT... should look like. You need to test the SELECT yourself using several sets of parameters. You're getting multiple rows returned sometimes. Do they have the same or different values of company?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden