help: cursors in SQL Server 2000 (compared to oracle) 3

[alfredjp]

im quite of an oracle guy, and ive been using cursors for my stored procedures, etc.

now that a client has chosen to use sql server 2000 for the back-end database, well, obviously, i have to relearn to do things in the new platform.

one thing i do find (could someone correct me in this) is the poor implementation of cursors in sql server (as compared to oracle).

1. there is nothing similar to ROWTYPE which would allow me to take a record from a table with numerous columns - in sql server, i have to define each and every column before i can use the INTO clause
2. in order to loop through the cursor in sql server, i have to execute FETCH FROM once BEFORE entering a LOOP, then do it once more INSIDE the loop
3. i need to execute TWO LINES of CODE just to "disable" a cursor

talk about inefficiency... but if someone could point me to better methods, processes, etc. i would really appreciate it. or some urls that i could check and somehow change my way of thinking.

as of now, im really quite frustrated.

thanks.

 

[JohnDTampaBay]

I think everyone on this forum will agree that cursors are extremely inefficient. Processing data one row at a time is very resource intensive. If at all possible, design stored procedures and/or triggers to do set-based operations. Most operations can be accomplished without cursors.

A wise guru once told me, "Set-based is the true path, Grasshopper."

If you have a specific example, we'll be glad to help convert a cursor to a set-based operation. Good luck!

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[alfredjp]

im must apologize for the late reply... been really held-up at work to check up on my boards...

to AngelWPB:

what i would really like to accomplish is how to implement "UPDATE-INSERT" processes. i guess that once i have found an efficient way to doing this in T-SQL, then everything else would follow. im quite sure that you know what an "UPDATE-INSERT" process is --> i check every ONE incoming row, and see if it is in the destination table. if it is, UPDATE. if NOT, then do an INSERT.

the problem i encounter is that i deal with tables with LOTS of columns, and with T-SQL not having a "ROWTYPE" functionality, i end up with messy code with declarations of each and every field.

thank you very much.

 

[SQLSister]

No cursors needed or desired. Cursors are extremely slow and inefficent. They should not be used for simple set statements. One update statement to update all the rows which match the base table and one insert statement to insert the rows which do not match. Do the update first of course or all the rows will match.

Here's the code I would use for one of my tables to do your task. The AirportID field is the primary key for this table and is what uniquely identifies if the record exists in the base table or Airport. To get the column names quickly, I used the object browser in Query Analyzer and dragged the word columns from the table I wanted to the query and dropped and then deleted any columns I didn't need in the statement. Granted it took longer to do the update statement, but it still didn't take very long. The beauty of drag and dropping the column names, is that you never misspell them.

Update Airport
Set AirportType = a.AirportType,
Set SetInfoEffDate = a. SetInfoDate,
Set FAARegCode = a.FAARegCode,
Set State = a.State,
Set City= a.City,
Set AirportName = a.AirportName,
Set OwnershipType = a.OwnwershipType,
Set FacilityUse = a.FacilityUse,
Set FacilityOwner = A.FacilityOwner,
Set OwnerAddress = a.Owner Address,
Set OwnerLocation = a.OwnerLocation,
Set OwnerPhone = a.OwnerPhone,
Set ManagerName = a.Managername,
Set ManagerAddress = a.Manager Address,
Set ManagerLocation = a. ManagerLocation,
Set ManagerPhone = a.ManagerPhone,
Set Latitude = a.Latitude,
Set Longitude = a.Longitude,
Set AiportLatitudeF = a.AiportLatitudeF,
Set AirportLongitudeF = a.AirportLongitudeF,
Set Elevation = a.Elevation,
Set MagneticVariation = a.MagneticVariation
From AirportUpdate a
Where a.AirportID = Airport.AirportID

Insert Airport (AirportType, AirportID,
InfoEffDate, FAARegCode, State, City, AirportName,
OwnershipType, FacilityUse, FacilityOwner,
OwnerAddress, OwnerLocation, OwnerPhone,
ManagerName, ManagerAddress, ManagerLocation,
ManagerPhone, Latitude, Longitude, AiportLatitudeF,
AirportLongitudeF, Elevation, MagneticVariation)
Select AirportType, AirportID,
InfoEffDate, FAARegCode, State, City, AirportName,
OwnershipType, FacilityUse, FacilityOwner,
OwnerAddress, OwnerLocation, OwnerPhone,
ManagerName, ManagerAddress, ManagerLocation,
ManagerPhone, Latitude, Longitude, AiportLatitudeF,
AirportLongitudeF, Elevation, MagneticVariation
from AirportUpdate Left join Airport
On AirportUpdate.AirportID = Airport.AirportID
Where Airport.AirportID is Null

By the way, whining about how things were so much easier in a different programming language is not likely to get you much sympathy. Everyone who has ever used more than one language has had things they like better in another language. Big Deal. Get over it. Eventually if you have an open mind, you will find things that you like better about the SQL Server implementation.

 

[alfredjp]

thanks for the reply.

ill try to keep a more open mind.

hopefully with your guys' support, i think i may begin to love sql server.

(^^)

 

[SemperFiDownUnda]

you're not upset with my reply in thread183-541400 are you?

As I pointed out in my post in there things you HAVE to use cursors for in Oracle you don't have to do in SQL server.

You look at the
CLOSE my_cursor
DEALLOCATE my_cursor
as logically 1 statement but it isn't
You can CLOSE as cursor do some other stuff and then open it back up.

The thing is ORACLE has a different methodology with storeprocs than SQL server does. It has its strengths and weaknesses but all in all I, from a programming point of view perfer SQL Server. I just wish SQL Server would impliment ROWNUM.

I'm not sure if Oracles cursors work the same as SQL server (I hope not) but every time I see a stored proc to pass back data to the client like this i cringe.

PROCEDURE GetEmpRecords(indeptno IN NUMBER,
p_errorcode OUT NUMBER,
p_cursor OUT empcur)
IS
BEGIN
p_errorcode := 0;

IF indeptno != '' THEN
   OPEN p_cursor FOR 
	 SELECT WEB_ID, CODE, NAME 
	 		FROM WEB 
			WHERE WEB_ID = indeptno
 			ORDER BY NAME;
ELSE
	OPEN p_cursor FOR 
	 SELECT WEB_ID, CODE, NAME 
	 		FROM WEB 
 			ORDER BY NAME;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_errorcode := SQLCODE;
END GetEmpRecords;
END employees_msft;

who knows SQL may do the same type of thing behind the scenes.

 

[alfredjp]

TO SemperFiDownUnda (Instructor)
> you're not upset with my reply in Thread183-541400 are you?

thanks again for the reply... with so many replies, there are just so many things i have to think over... but the good side is that I am learning... thanks to you guys...

as for me being upset... no... no... im not upset to your reply to Thread183-541400... you would say that i was hoping for a more specific solution to a certain dilema...

my post regarding "help: cursors in SQL Server 2000 (compared to oracle)" is actually due to me starting out in SQL Server, while my post regarding ""anchored declaration" in T-SQL?" is actually due to a specific problem that i must solve.

i hope that i havent estranged anyone in this group. m(_ _)m

 

[SemperFiDownUnda]

Nah not me...my sarcasim can come off wrong often. I wish I could find the code I was looking for to help you but I fear its at my last contracts business.

Basically yes you have to wear redeclaring variables if the column that pumps data into them changes. This means that you have to modify all the storeprocs that use the column in a cursor. But on a happy note you often don't have to use a cursor so you don't have the problem anyway.

I'm with you, I get frustrated with Oracle because I don't know it enough.

Maybe give us a stored proc you are playing with cursors on oracle and have a few of use make suggestions on the SQL server implimentation. I guess its like a high rise construction worker going to build a house with his rivet gun and every time he uses it on a 2x4 it breaks everything. We don't have to show you how to use a rivet gun to build a house but need to show you how to use a different approach (nail gun) that will fit better with the architechure here. Hows that for an analogy 8)

 

[SQLSister]

Who us? We don't often get estranged from anyone.

 

[alfredjp]

To SemperFiDownUnda (Instructor):

yes, it seems that oracle and sql server have different perspectives in their approach to cursors.

anyway, what i would like to achieve is an "update-insert" process.

taking hint from SQLSister, take an example wherein we have the following code:

CREATE PROCEDURE prc_TestUpdateInsert
AS
  -- update process first
  UPDATE T_Destination_Table
  SET
    Product_Name = Src.Record_Name,
    Product_Value = Src.Record_Value
  FROM
    T_Source_Table AS Src
  WHERE
    Src.Record_ID = T_Destination_Table.Product_Code

  -- insert process second
  INSERT INTO T_Destination_Table (
    Product_Code,
    Product_Name,
    Product_Value
  )
  SELECT
    Src.Record_ID,    -- Record_ID maps to Product_Code
    Src.Record_Name,
    Src.Record_Value
  FROM
    T_Source_Table AS Src LEFT JOIN T_Destination_Table
  ON Src.Record_ID = T_Destination_Table.Product_Code
  WHERE
    T_Destination_Table.Product_ID IS NULL
GO

as one would note the above "UPDATE" would AFFECT all the rows in the destination table. fairly ok if we are dealing with few records, but what IF there are so many records in the table.

is there a possible way of only updating the records that has actually changed?

using some sort of an "archived" flag on the source table wouldn't work because that particular table is located on a system that i have "no-touch" privileges.

 

[alfredjp]

in regards to the above post, i think that it would be better to treat this as another new thread...

dont you think so?

 

[SemperFiDownUnda]

Umm I'm not sure this would work

UPDATE T_Destination_Table
SET
Product_Name = Src.Record_Name,
Product_Value = Src.Record_Value
FROM
T_Source_Table AS Src
WHERE
Src.Record_ID = T_Destination_Table.Product_Code


I think it should look like this, which also only effects the needed rows

UPDATE T_Destination_Table
SET Product_Name = Src.Record_Name,
Product_Value = Src.Record_Value
FROM T_Destination_Table A
INNER JOIN T_Source_Table AS Src
ON Src.Record_ID = A.Product_Code

It may I just not sure.

But another not is maybe you should look at triggers to do this. Some differences between Oracle Triggers and SQL triggers are
1) SQL Server support multiple seperate triggers for a single table/DML statement.
ie I can have 5 different Update Triggers for the Customer table. They each have different names. Note there is no way to tell what order the triggers will fire off in.
2) SQL Server triggers fire of only 1 time for a given statement where Oracle fires of 1 time for ever row effected.
ie There are 2 memory tables available inside the trigger. They are called "Inserted" and "Deleted". The Inserted Table has 1 row for every row that was effected by the original statement. The Deleted Table has 1 row for every row that was effected. A DELETE statement the Inserted table will be empty, for an INSERT statement the DELETED table will be empty, for an UPDATE statement the DELETED table will contian the before image and the INSERTED will contian the after.
Triggers can be set up so they can cause recursion. If you have the need make sure you check the level of recursion as SQL Server only supports nesting 32 level at which time the 33 would fail and roll back the entire statement.

SQLSister anything to add?

Sorry if i'm off base I'm short on time and just blurting out a answer to the question i think I read when I skimmed over the last post.

 

[alfredjp]

yes for the reply, and the correction in my code (i rarely get to use ANSI syntax in oracle)...

and yes, i could have used triggers in the code so for any change in the source table would automatically be reflected in the destination table, unfortunately, the head designer mentions that it should be a batch process that runs at night...

 

[alfredjp]

To SemperFiDownUnda (Instructor):

regarding your code below... i was just wondering, though...

UPDATE T_Destination_Table
     SET Product_Name = Src.Record_Name,
         Product_Value = Src.Record_Value
    FROM T_Destination_Table A
    INNER JOIN T_Source_Table AS Src
    ON Src.Record_ID = A.Product_Code

i tried it out in query analyzer, and it always comes out with a message that all rows are affected... are this the actual rows that have been "touched" by the above statement?

or maybe, i dont really understand the messages presented by query analyzer...

 

[SemperFiDownUnda]

if there is a record in T_Source_Table for every record in T_Destination_Table then yes every row will be effected. Usually it just tells you the number no that "All" are effected.

 

[SQLSister]

Do you have some indicator that tells you which rows have changed like a last modified date field? If not I suspect, it will be faster just to update all the rows than to check the contents of every field to see if they have changed.
But you could put a where clause onthe check for differences

Where a.field1 <> b. field1 or a.field2 <> b.field2, etc.

Then check to see which query is faster, the total update or the one with the where clause.

I have a book that might help you in your effort to move from SQL Server to Oracle. It's called SQL in a NUtshell by Kevin and Daniel Kline. It has the SQL server and Oracle syntax for the major SQL commands (Also mysql and POstgreSQL) andiwould be helpful for you to see the differences in how the two programs handle things.

 

[alfredjp]

If not I suspect, it will be faster just to update all the rows than to check the contents of every field to see if they have changed.

yeah... it seems that to just update the entire table seems to be faster, but with an unconfirmed raw data from the client, there is no production-level (ie., meaning, a HUGE chunk) way to test it... but ill try to look at it more closely...

I have a book that might help you in your effort to move from SQL Server to Oracle. It's called SQL in a NUtshell by Kevin and Daniel Kline. It has the SQL server and Oracle syntax for the major SQL commands (Also mysql and POstgreSQL) andiwould be helpful for you to see the differences in how the two programs handle things.

thanks... ill check that particular book out...

 

[SemperFiDownUnda]

Just make sure you get the right book....I made the mistake of getting a book by Calvin Klein .....didn't help me a bit.

Sorry for the bad joke....its a bad day.

 

[alfredjp]

Just make sure you get the right book....I made the mistake of getting a book by Calvin Klein .....didn't help me a bit.

Sorry for the bad joke....its a bad day.

sure... no problem... (^^)
it actually made me look through the previous post if it was indeed calvin klein