SQL Cursor vs. ActiveX Script Task?
-
Hello All. I was wondering if it would be preferable to use either a CURSOR or an ActiveX Script for a process where I need to Loop through records I'm importing from a text file, and check criteria row-by-row, and field-by-field and then perform either INSERTS, UPDATES, OR Nothing at all. For Example:
''''''''''''''''''' START DO WHILE OR FOR NEXT LOOP
'''''''''' If there is no matching record then INSERT INTO DB
IF the PK is not found in the db THEN
INSERT entire record
END IF
'''''''''''
IF the PK has lesser date/time THEN
SKIP INSERT/UPDATE
END IF
'''''''''' Criteria Check
IF (XactionVRU.FaxReqPhoneNum <> '000000000000') AND (XactionWeb.FaxReqPhoneNum = '000000000000') OR (XactionVRU.CurrentTime > XactionWeb.CurrentTime) THEN
UPDATE XactionWeb
SET XactionWeb.InternationalPhoneFlag = XactionVRU.InternationalPhoneFlag
SET XactionWeb.FaxReqPhoneNum = XactionVRU.FaxReqPhoneNum
'''''''''''''''''''Alternatively in ActiveX
DTSDestination("InternationalPhoneFlag") = DTSSource("InternationalPhoneFlag")
DTSDestination("FaxReqPhoneNum") = DTSSource("FaxReqPhoneNum")
END IF
END
I'm guessing that you can use either using a LOOP in ActiveX or a WHILE @@FETCH_STATUS LOOP in a Cursor, but before I dive in, I hoped to get some input from some of you, in case one is better, or one is not as adequate. If you have a suggestion, or better yet, an example from your own experience, that would be great. Thanks in advance, guys.
-Pat
-
My suggestion is NOT to use cursor nor an ActiveX.
First, load your data into a load table from the file.
Second, run a scrubbing SP that would bring your columns in proper format. You can use UPDATE with CASE or WHERE clauses, or other techniques but do not loop. I am sure there are common patterns in your processing therefore multiple records could be processed simultaneously, not one at a time.
Third, validate your rows in the same manner you scrabbed those, using WHERE clause, etc. You may have one or two SELECT stratements per validation rule.
Do not forget to log errors/validation failures.
Fourth, INSERT, UPDATE, or DELETE all the rows that need to be inserted, updated or deleted at once. All at once! You may need to mark them for certain operation before and use the mark in the WHERE clause.
However, if your data load files always contain only 10 (or 11) rows, all these suggestions do not matter, just use a cursor and never mind my long speach.
Thank you.
Michael
Edited by - mromm on 01/28/2003 11:38:31 AM
-
actually, they will usually be pretty small like 10 to 20 rows. You feel a cursor is better? Do you have an example? Thanks for the response.
-
Set based is the way to go, if possible.
-
Personally, I would stick to the load tables as well. Even for small jobs. What I have found is that small jobs end up growing over time and your cursors will start eating up your performance quick. I do all my text loads into a single column table and then use substrings to pull out the records I want from each row. For small jobs 10 or 20 rows I doubt you are going to see huge time diffrences between a cursor or using an ActiveX job.
Wes
-
My rule is never use cursors.
Well, there are rear exclusions but this is not the one. It is healthier for your production environments to forget completely cursors ever existed.
I would not recommend reading all the columns into one. What is the point? It does not help in the following steps including scrabbibg, standardizing, validating, etc.
Michael
-
Thanks for the responses. I got side-tracked. I'm still having trouble with this, and could really use some help.
Here is an example of a regular Update on the table I'm working on. It works.
UPDATE Xaction
SET
InternationalPhoneFlag = v.InternationalPhoneFlag,
FaxReqPhoneNum = v.FaxReqPhoneNum,
OutstandingMailReqFlag = v.OutstandingMailReqFlag,
MailReqDate = v.MailReqDate,
FaxReqFlag1 = v.FaxReqFlag1,
FaxReqFlag2 = v.FaxReqFlag2,
FaxReqFlag3 = v.FaxReqFlag3,
FaxReqFlag4 = v.FaxReqFlag4,
FaxReqFlag5 = v.FaxReqFlag5,
FaxReqFlag6 = v.FaxReqFlag6,
FaxReqFlag7 = v.FaxReqFlag7,
FaxReqFlag8 = v.FaxReqFlag8,
FaxReqFlag9 = v.FaxReqFlag9,
WebOrVoiceFlag = v.WebOrVoiceFlag,
MailReqFlag1 = v.MailReqFlag1,
MailReqFlag2 = v.MailReqFlag2,
MailReqFlag3 = v.MailReqFlag3,
MailReqFlag4 = v.MailReqFlag4,
MailReqFlag5 = v.MailReqFlag5,
MailReqFlag6 = v.MailReqFlag6,
MailReqFlag7 = v.MailReqFlag7,
MailReqFlag8 = v.MailReqFlag8,
MailReqFlag9 = v.MailReqFlag9,
MailReqFlag10 = v.MailReqFlag10,
MailReqFlag11 = v.MailReqFlag11,
MailReqFlag12 = v.MailReqFlag12,
MailReqFlag13 = v.MailReqFlag13,
MailReqFlag14 = v.MailReqFlag14,
MailReqFlag15 = v.MailReqFlag15,
MailReqFlag16 = v.MailReqFlag16,
MailReqFlag17 = v.MailReqFlag17,
MailReqFlag18 = v.MailReqFlag18,
MailReqFlag19 = v.MailReqFlag19,
MailReqFlag20 = v.MailReqFlag20
FROM XactionVRU v JOIN Xaction x ON (v.SSNum = x.SSNum)
WHERE v.CurrentDate = x.CurrentDate AND
v.After3Flag = x.After3Flag AND
v.CurrentTime > x.CurrentTime
Here is an example of a conditional update of one column for the same table. This works.
UPDATE Xaction
SET FaxReqPhoneNum = CASE when x.FaxReqPhoneNum = 0 then v.FaxReqPhoneNum
END
FROM XactionVRU v JOIN Xaction x ON (v.SSNum = x.SSNum)
WHERE v.CurrentDate = x.CurrentDate AND
v.After3Flag = x.After3Flag AND
v.CurrentTime > x.CurrentTime
However, I need to do this same sort of check on more or less all columns, provided the key is the same and the time stamp is later.
This does not work.
UPDATE Xaction
SET FaxReqPhoneNum = CASE when x.FaxReqPhoneNum = 0 then v.FaxReqPhoneNum
END
SET InternationalPhoneFlag = CASE when x.InternationalPhoneFlag = 0 then v.InternationalPhoneFlag
END
FROM XactionVRU v JOIN Xaction x ON (v.SSNum = x.SSNum)
WHERE v.CurrentDate = x.CurrentDate AND
v.After3Flag = x.After3Flag AND
v.CurrentTime > x.CurrentTime
I get incorrect syntax errors.
Does anyone know what I'm doing wrong or how I could go about doing this differently. I'm open to any suggestions, even if it's a completely different method altogether. I was just taking mrromm's advice. I also was working on a cursor, but put that aside for the time being. Any help is appreciated. Thanks.
-Pat
-
UPDATE Xaction
SET FaxReqPhoneNum = CASE when x.FaxReqPhoneNum = 0 then v.FaxReqPhoneNum
END
, InternationalPhoneFlag = CASE when x.InternationalPhoneFlag = 0 then v.InternationalPhoneFlag
END
FROM XactionVRU v
JOIN Xaction x
ON v.SSNum = x.SSNum
AND v.CurrentDate = x.CurrentDate
AND v.After3Flag = x.After3Flag
AND v.CurrentTime > x.CurrentTime
-
Thank You!! I was making that a bit harder than it was. One more question, please?
What if instead of the CASE Statement, I needed to check both fields, for instance,
UPDATE Xaction
SET
IF (v.FaxReqPhoneNum <> '000000000000000000') AND (x.FaxReqPhoneNum = '000000000000000000')
BEGIN
SET InternationalPhoneFlag = v.InternationalPhoneFlag,
FaxReqPhoneNum = v.FaxReqPhoneNum
END,
MailReqDate = v.MailReqDate
FROM XactionVRU v
JOIN Xaction x
ON v.SSNum = x.SSNum
AND v.CurrentDate = x.CurrentDate
AND v.After3Flag = x.After3Flag
AND v.CurrentTime > x.CurrentTime
I'm a little off there. Is there some syntax mistake I'm making there, too. I really, really appreciate this help.
-
Then you would have to duplicate the same condition check in more than one CASE statement.
Edited by - mromm on 01/30/2003 1:34:50 PM
-
UPDATE Xaction
SET InternationalPhoneFlag =
CASE WHEN v.FaxReqPhoneNum <> '000000000000000000'
AND x.FaxReqPhoneNum = '000000000000000000'
THEN v.InternationalPhoneFlag
ELSE x.InternationalPhoneFlag
END,
FaxReqPhoneNum =
CASE WHEN v.FaxReqPhoneNum <> '000000000000000000'
AND x.FaxReqPhoneNum = '000000000000000000'
THEN v.FaxReqPhoneNum
ELSE x.FaxReqPhoneNum
END,
MailReqDate = v.MailReqDate
FROM XactionVRU v
JOIN Xaction x
ON v.SSNum = x.SSNum
AND v.CurrentDate = x.CurrentDate
AND v.After3Flag = x.After3Flag
AND v.CurrentTime > x.CurrentTime
Edited by - mromm on 01/30/2003 1:42:05 PM
Edited by - mromm on 01/30/2003 1:43:01 PM
-
Thanks a lot, mromm. That was a great help. I'm still tring to learn the ins and outs of SQL Server, and have no one at work to ask questions about it. Thanks for taking out time to help, and I'm glad this site exists.
-Pat
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply