Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

UPDATE with CURSOR

Posted on 2004-11-06
24
Medium Priority
?
3,067 Views
Last Modified: 2008-01-09
Hi,

I have a sp using cursor for importing datainto SQL.  It has been fine.  Recently, I need to do update in this SP.  But it is slow, I was wondering if there are any solutions.  thx
0
Comment
Question by:mcrmg
  • 11
  • 5
  • 3
  • +2
24 Comments
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 12514623
how can u do the Update Statement ?

Can u post the Code ? for an Optimization SQL Code ll be helpfull

Melih SARICa
0
 

Author Comment

by:mcrmg
ID: 12514791
hi,

the following is the structure of mu sp, of course, it is much longer than this.  I use the normal way to update table, if it does not exist, insert, else, update it.  please have a look. thx






DECLARE @cStaging CURSOR
SET @cStaging = CURSOR FOR
SELECT "Something here"
FROM "Something here"

OPEN @cStaging

FETCH NEXT FROM @cStaging INTO "Something here"

BEGIN TRAN T1
WHILE @@FETCH_STATUS = 0
BEGIN

--LoanInfo_Static
IF NOT EXISTS ("Something here")
      BEGIN
         INSERT INTO "Something here"
      END
ELSe
      BEGIN
         SELECT @S_myID = myID FROM someTable WHERE "Something here" ....................>>>>>>>>>>>>I need to grap @S_myID here because it will  be used later

         UPDATE MyTable.............................................>>>>>>>>>>>>>>.here
         SET "Something here"
                   WHERE myID = @S_mYID
      END


   FETCH NEXT FROM @cStaging INTO "Something here"

END






















0
 
LVL 8

Expert Comment

by:SashP
ID: 12515075
It does not appear that you need to use a cursor from what you have shown, although I can appreciate that you have tried to simplify it.

Have you tried

INSERT INTO "Something Here"
WHERE "Criterea"

AND

UPDATE MyTable
SET "Something here"
FROM MyTable mt
JOIN SomeTable st on st.myID = mt.myId
WHERE st."something here"

Try to avoid cursor they are a performance overhead that should be avoided.

Cheers Sash
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mcrmg
ID: 12515125
hi,

Before I created that cursor, I have tried different ways to avoid it...  but for some reason(dont remember now)  lol,
only using cursor can get y data imported completely.  (I know I need to spend time on this sp by not using cursor.lol)

However, my boss want me to lunch this asap, so I am thinking when this is up and I have some quiet time, I will fix that.  In the meantime, if I use the current sp, is that a way to make update quicker?? thx


0
 
LVL 8

Expert Comment

by:SashP
ID: 12515142
mcrmg,

Get it running with the cursor, accept the performance issue.   Then work on replacing the cursor.

Sash
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12515931
You might be supprised at how quickly some of the SQL experts can turn a huge cursor into a small update.  Post the entire procecure. Someone will probably be able to show you how to do it without a cursor.

In general what you are doing might be optimized with;

SELECT a."Something here", b.b_key
into #tmp
FROM "Something here" a
outer join 'something here you would insert into' b on a.key = b.key

insert into bsomething (something)
select a.something from #tmp where b_key is null

update mytable
set someotherthing = #tmp.something
from #tmp
where b_key is not null

But I really confuse myself with all the 'somethings' so real code would help :)  But it can probably be done even more efficiently.
0
 

Author Comment

by:mcrmg
ID: 12519170
okay, I guess I will need help on the basic structure.

I import a txt file into a staging table in SQL, then I use cursor to loop through that staging and insert them into different table.

How can I acieve this without using cursor?  thx










0
 
LVL 8

Expert Comment

by:SashP
ID: 12519183
mcrmg,

You will need to post the complete stored proc for us to be able to assist you with it.

Sash
0
 

Author Comment

by:mcrmg
ID: 12519281
I hope this will help..thx




ALTER                             PROCEDURE st_IMPORT(@AID int, @BID int, @CID int)

 AS
BEGIN

DECLARE @cStaging CURSOR
SET @cStaging = CURSOR FOR
SELECT @AID, @BID, @CID, [Field1],[Field2],[Field3]
FROM EXCEL_Source

OPEN @cStaging

FETCH NEXT FROM @cStaging INTO @AID, @BID, @CID, @S_Field1,  @S_Field2, @S_Field3

BEGIN TRAN T1
WHILE @@FETCH_STATUS = 0
BEGIN


IF NOT EXISTS (SELECT * FROM myTable1 Field1 = @S_Field1 AND AID = @AID AND BID = @BID)
      BEGIN
      
         INSERT INTO myTable1 Syntax here
        
set @MyErr = @@Error
IF @MyErr <> 0 set @AnyErr = @MyErr

      END
ELSe
      BEGIN
         SELECT @myTablePK = myTableID FROM myTable WHERE Field1 = @S_Field1 AND AID = @AID AND BID = @BID

         UPDATE myTable1 Syntax here
         WHERE myTableID = @myTablePK
      END


   FETCH NEXT FROM @cStaging INTO @AID, @BID, @CID, @S_Field1,  @S_Field2, @S_Field3

END

if @AnyErr = 0
      BEGIN
       print 'TRANSACTION SUCCESSFULLY!!'
         COMMIT TRAN T1
      END
else
      BEGIN
       print 'ERROR OCCURED: TRANSACTION ROLLBACKED!!!!!!!!'
         ROLLBACK TRAN T1
      END



CLOSE @cStaging

DEALLOCATE @cStaging




END


0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12519437
The detail will depend on the  "Syntax Here" bit, but in principle, there is no need to use a cursor to do what you want - you can do it with one INSERT statment, and one UPDATE statement along these lines - If you want more detailed help, please pst the full code.

ALTER PROCEDURE st_IMPORT(@AID int, @BID int, @CID int)
AS
BEGIN
        INSERT INTO myTable1 Syntax here
      WHERE NOT EXISTS (SELECT 1 FROM MyTable1 JOIN Excel_Source
      ON MyTable1.Field1 = Excel_Source.Field1
      AND MyTable1.Field2 = Excel_Source.Field2
      AND MyTable1.Field3 = Excel_Source.Field3
       )

      UPDATE myTable1 Syntax here
      FROM myTable1 JOIN Excel_Source
      ON MyTable1.Field1 = Excel_Source.Field1
      AND MyTable1.Field2 = Excel_Source.Field2
      AND MyTable1.Field3 = Excel_Source.Field3

END
0
 
LVL 19

Assisted Solution

by:Melih SARICA
Melih SARICA earned 400 total points
ID: 12519555
Try this..

Its not an Cursor but ll work better I guess
Create Procedure st_IMPORT(@AID int, @BID int, @CID int)
as
declare @AnyError int
set @AnyError = null

Begin Trans T1
insert into MyTable
          select Es.Field1 , Es.AID ,Es.BID , <Other Columns Here>  from EXCEL_Source Es
                left Join MyTable Mt on Mt .Field1 = Es.Field1 AND Mt AID = Es.AID AND Mt BID = Es.BID AND Mt CID = Es.CID
                where Mt.AID is null
                            and Es.AID = @AID and Es.BID =@BID and Es.CID = @CID
if @@Error <> 0
begin
  set @AnyError = @@Error
end
else
begin  
   Update MyTable
        set  Mt .Field1 = Es.Field1 , Mt AID = Es.AID , Mt BID = Es.BID <Fields ll Updated must be here instead of these Fields>
        from MyTable mt inner join Excel_Source Es
        on Mt .Field1 = Es.Field1 AND Mt AID = Es.AID AND Mt BID = Es.BID
  if @@Error <> 0
      begin
      set @AnyError = @@Error
      end
end

if  @AnyError <> null
begin
   Rollback Trans T1
   print ' Error Occured TranSaction RollBacked'
else
  Commit Trans t1
   print ' Transaction Completed'
End


Melih SARICA
0
 

Author Comment

by:mcrmg
ID: 12519773
@BillAn1,

So, UPDATE will get fired everytime even if the new record get inserted??  thx
0
 

Author Comment

by:mcrmg
ID: 12520051
If I am noy using CURSOR, is there a way that I can loop through that staging table?  thx
0
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 1000 total points
ID: 12521821
"So, UPDATE will get fired everytime even if the new record get inserted??"
If you want it to fire automatically, you need a trigger.

"is there a way that I can loop through that staging table?"
you dont want/need to loop through the staging table. A set-based piece of SQL can do the whole thing in one go, you do not need to actually do it one row at a time.
0
 

Author Comment

by:mcrmg
ID: 12522764
>A set-based piece of SQL can do the whole thing in one go, you do not need to actually do it one row at a time.

Can you show me how??  thx

One of the reasons that I used cursor was because I do not know how to loop through the staging table properly......  thx again
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 1000 total points
ID: 12524821
SQL is 'set based'. That means that it will automatically run for multiple rows etc, it does not need anything specific to tell it to loop through a table. Hence, the examples given will effectvely 'loop through'

If you give more detail on what you want to actually happen, the 'Syntax Here' component, we can help structure your query for you.

As a more complete example, see below for waht I mean.

suppose you have a simple table called Table1
Col1 Col2
 1       2
 2       4
 3       5
 4       3

and a 2nd table called Table2
Col1 Col4
  1    6
  3    7
  9    9


You want Table2 to contain a row for each value of Col1 from Table1,
and the value of Col4
should be incremented by 1 if there is already a record,
otherwise insert a new one, and set the value to 1
plus, leave any records that already exist, but do not have a corresponding record in Table1.

i.e. the final result you want in Table2 is
   1     7
   2     1
   3     8
   4     1
   9     9

so, first off - do the update

UPDATE Table2
SET Col4 = Col4 + 1
WHERE EXISTS (SELECT 1 FROM Table1 WHERE Table1.Col1 = Table2.Col1)

Secondly, do the inserts

INSERT INTO Table2
SELECT Col1, 1
FROM Table1
WHERE NOT EXISTS (
      SELECT 1 FROM Table2 WHERE Table1.Col1 = Table2.Col1
)
0
 
LVL 12

Expert Comment

by:Ken Selvia
ID: 12524878
I'm still watching this thread and feel compelled to say for what seems like the 4th time, if you will post the entire, actual, real, complete procedure, someone will be able to show you exactly how to eliminate the cursor.
0
 

Author Comment

by:mcrmg
ID: 12524992
@kselvia,

I know that in order to help EEs to help me, it is better to have more details.  If I posted all the code, my boss will kill me right away.......   lol
So, I will do my best............  thx for the understanding....


@BillAn1,

So, by your example, SQL will go through Table1 by itself, I do not need to do any coding for the "looping through part"?  thx
0
 
LVL 17

Assisted Solution

by:BillAn1
BillAn1 earned 1000 total points
ID: 12525102
yes, try not to think of SQL as procedural. An SQL statement is normally set-based, it effects multiple rows (depending on what you specify in your WHERE clause) in one go, try not to think of it as looping through, it does all rows "simultaneously" (at least conceptually........)

and, as kslevia says, the more detail you can give, the more help we can provide....
0
 

Author Comment

by:mcrmg
ID: 12525124
okay, I will try that and post back....thanks for EEs' help.......
0
 

Author Comment

by:mcrmg
ID: 12525405
Since I have several tables that I need to insert or update(EE has help me on the update part)

For the rest of the tables, I want to force sql to do the insert, for ex, for table3, I do not want to check if the record exists in db or not, I want to insert into table3 from table1.  thx
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 12525458
then the basic syntax is just as follows :
insert into Table3 (col1, col2, col3)
select col1, col2, col3
from table1
0
 

Author Comment

by:mcrmg
ID: 12525466
Thank you.
0
 
LVL 19

Expert Comment

by:Melih SARICA
ID: 12528332
mcrmg.. check the codes that i ve posted above


Melih SARICA
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question