May 23, 2008 at 1:45 pm
Hi guys,
I have a stored procedure which returns some result, now I am wondering how do I do this:
if not exists (select * from [spGetHighLevelScoreForSpecificSerialNoWithNameNew] @serial_no)
.......
[spGetHighLevelScoreForSpecificSerialNoWithNameNew] @serial_no will return result like
UserName, Serial_No, UserType, AV, FW, MP
The purpose of checking this is:
I have a cursor which get all serial_no and then for each serial_no I need to run
INSERT INTO DailyScore (UserName, Serial_No, UserType, AV, FW, MP)
exec [spGetHighLevelScoreForSpecificSerialNoWithNameNew] @serial_no
but I want to avoid inserting duplicated record.
Thanks.
May 23, 2008 at 1:53 pm
So your SP returns the values for 1 serial_no at a time and your cursor does an INSERT for each serial_no?
It does not sound like you need to use a cursor to make this work. What is your SP doing?
May 24, 2008 at 1:51 am
You mean, you want to insert only, if the record does not exist?
Use left outer join. If it must be a procedure, convert it to table function and insert from join using apply operator.
See my post here:
http://www.sqlservercentral.com/Forums/Topic498944-361-2.aspx?Update=1
Using apply
May 24, 2008 at 8:38 am
I'm not sure a function is needed for an insert. The left outer join suggestion is a good one, though.
Usually what I do is...
Insert into MyTable (col1, col2, col3)
(Select t1.col1, t1.col2, t1.col3
from Table1 t1
left outer join Table2 t2
on t1.ID = t2.ID
where t2.ID is NULL)
or something to that effect. It easily fits into a stored procedure and you don't have to worry about possible row-by-row evaluation of a function.
May 27, 2008 at 9:02 am
Yep, I'm with Brandie here. No function, no cursor, just an INSERT statement. If you could give us more information, we could be of more help. If you need further assistance, please post table DDL, sample data, and desired results.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply