January 30, 2012 at 10:22 am
Hi All, i have this cursor:
DECLARE @cod INT
DECLARE @getlista CURSOR
SET @getlista = CURSOR FOR
SELECT id_recurso_detalle from @RecursosAnalisis95
OPEN @getlista
FETCH NEXT from @getlista INTO @cod
WHILE @@FETCH_STATUS=0
BEGIN
IF (SELECT count(*) from spotrecursoanalisis WHERE id_recurso=@cod and id_oferta=@pid_oferta)=0
BEGIN
INSERT <table>
ELSE
BEGIN
UPDATE <table>
END
FETCH NEXT from @getlista INTO @cod
END
CLOSE @getlista
DEALLOCATE @getlista
anyone can optimize this?, change the cursor by other query better... and get best perfomance..
the inserts are about 15 columns, and the update like 7.
Thanks.
____________________________________________________________________________
Rafo*
January 30, 2012 at 10:51 am
Are you familiar with the new MERGE statement (Introduced in SQL 2008) ?
May I suggest looking at:
http://www.simple-talk.com/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/
and:
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
January 30, 2012 at 11:57 am
Don't use a cursor for this, it is not needed. You can use MERGE as the previous person said, or simply do an insert with a left join.
INSERT INTO TableA (columnA, ..., columnN)
SELECT B.columnA, ..., B.columnN
FROM TableB B
LEFT JOIN TableA A
ON B.columnA = A.columnA
WHERE a.columnA IS NULL
Cursor here is not needed since you can do this as a set operation.
Jared
CE - Microsoft
January 30, 2012 at 12:06 pm
Sorry, the SQLSERVER it's 2005.
____________________________________________________________________________
Rafo*
January 30, 2012 at 12:08 pm
Well then, use my code sample from above for insert. You can do the opposite for deletes. For updates, use an inner join.
Jared
CE - Microsoft
January 30, 2012 at 12:33 pm
A method which should work in 2005 (sample code copied from the 2008 help file)
USE AdventureWorks;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
@UnitMeasureCode nchar(3),
@Name nvarchar(25)
AS
BEGIN
SET NOCOUNT ON;
-- Update the row if it exists.
UPDATE Production.UnitMeasure
SET Name = @Name
WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
VALUES (@UnitMeasureCode, @Name)
END
END;
January 30, 2012 at 1:52 pm
if that have 3 upgrades of 4 records,
how do I insert the record was not updated??
____________________________________________________________________________
Rafo*
January 30, 2012 at 3:17 pm
xRafo (1/30/2012)
if that have 3 upgrades of 4 records,how do I insert the record was not updated??
Which posted possibly solution are you refering to?
If it is my posting - please re-read the comment, and the code beneath said comment
(repeating that section of the code)
-- Insert the row if the UPDATE statement failed.
IF (@@ROWCOUNT = 0 )
BEGIN
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name
VALUES (@UnitMeasureCode, @Name)
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply