Can you please help me??

  • I have some doubts in using of loops in sql server 2005

    can you please anyone help me?

    Thanks in advance

  • techmarimuthu (8/26/2013)


    I have some doubts in using of loops in sql server 2005

    can you please anyone help me?

    Thanks in advance

    Probably but you need to ask the actual question you have. You should also take a peek at the first link in my signature line below.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Don't use loops if you can avoid it. If you have a specific issue, as Jeff mentioned, we can help you find a way to do it without a loop.

  • techmarimuthu (8/26/2013)


    I have some doubts in using of loops in sql server 2005

    can you please anyone help me?

    Thanks in advance

    My general rule of thumb is:

    It's OK to use loops for manipulating database objects

    It's NOT OK to use loops for manipulating data

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • I respect the general wisdom about not using cursors and loops and have usually found a way to avoid them.

    But I have one data upload from a vendor in a CSV file. I pull it into a temp table in SS with an SSIS process, which then runs a stored procedure. The SP creates a cursor and loops through the temp file, evaluating the vendor's returned data. Depending on the content of each data point, I may write one thing to one table or another value to a different table.

    I don't know how I could possible build all that updating logic into any simpler form and it works well enough and quickly enough for me.

    So it seems to me there are times that a cursor and a loop are the only possible solutions. But I'm always ready to learn.

    Can anyone point me toward a discussion of why and how all cursors and loops can be done away with?

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • I don't know you can do away with all of them. Most you can, and there isn't a summary that I know of. There are examples of where a cursor is done away with, but why don't you present details on the way you handle the import? Perhaps there is a better way, perhaps not.

  • Following links might be of help to you:

    http://technet.microsoft.com/en-us/library/ms178642(v=sql.90).aspx

    http://technet.microsoft.com/en-us/library/ms141724.aspx

    There are lots of good articles on technet that you can refer to.

    If you can specify your actual doubts, someone can help you out 🙂

  • Sigerson (8/28/2013)


    I respect the general wisdom about not using cursors and loops and have usually found a way to avoid them.

    But I have one data upload from a vendor in a CSV file. I pull it into a temp table in SS with an SSIS process, which then runs a stored procedure. The SP creates a cursor and loops through the temp file, evaluating the vendor's returned data. Depending on the content of each data point, I may write one thing to one table or another value to a different table.

    I don't know how I could possible build all that updating logic into any simpler form and it works well enough and quickly enough for me.

    So it seems to me there are times that a cursor and a loop are the only possible solutions. But I'm always ready to learn.

    Can anyone point me toward a discussion of why and how all cursors and loops can be done away with?

    I know very little of SSIS. If I were doing this in T-SQL, I'd simply add a column that identified which table I'd want the row to go to. Then I'd do a single pass "INSERT" for each table. I don't know how you are evaluating which rows go to what table but even 5 passes because you have 5 tables is going to be an awful lot faster than using a loop against a file.

    Think "columns", not "rows".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • techmarimuthu (8/26/2013)


    I have some doubts in using of loops in sql server 2005

    can you please anyone help me?

    Thanks in advance

    You still haven't even identified what your doubts are. Are you not interested?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks to everyone who answered me.

    I didn't mean to imply that I have doubts about avoiding cursors and loops. I do avoid them whenever possible because I respect the advice of the people who say it's good practice. I will certainly read the recommended articles.

    But I'm still learning where to draw the line. Here's my current requirement, where I found that only a cursor provided the control I needed, given my skill set:

    I have a CSV of invoice information coming in from a vendor, and it gets moved into a temporary table in the d/b for the following steps:.

    1. I have to create and save to disk an RTF exception report which I create using the FSO.

    2. I have to examine every field in every row and print the row number and field name of any data that's invalid or missing on the exception report.

    3. If the file passes the validation phase, then I rewind the cursor and begin the update phase.

    4. If the account has been put on hold or closed out, print a message on the exception report.

    5. I write the invoice information to one table and four other values to two other tables.

    6. The names and addresses on the invoices may have changed from the information we sent the vendor, so changes must be identified and updated to the tables, and printed on the exception report.

    7. In one case, a decision on what to update is based on inspection of multiple interrelated fields. For all of these 'evaluated' updates, I have to print an explanatory message to the exception report.

    Doing all of that within one SQL statement or a With block is beyond me, so I used a cursor. But like I said, I'm always willing to learn.

    Thanks again,

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • I am certanly not a SSIS expert so I can't give you more details. Others on this forum might provide better help on that. But as far as I know you can create good conditional logic inside a SSIS package.

    So instead of importing the CSV to a temp table and then analyse and process the lines you could try to analyse and proces the lines inside SSIS and then writing the results to the specific tables.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Why not add an extra column as specified earlier and then apply your logic to a case statement to populate the column.

    You can then split the data out as required.

    Or use SSIS and a conditional Split or milti-cast.

  • Sigerson (8/29/2013)


    Doing all of that within one SQL statement or a With block is beyond me,...

    It's beyond me, too. But then again, I wouldn't even try to do it with a single statement. Avoiding the loop or doing something "set based" does [font="Arial Black"]NOT [/font]mean doing it "within one SQL statement or a Withblock". Multiple set-based passes will be much faster than using a loop for this.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden (8/29/2013)


    Sigerson (8/29/2013)


    Doing all of that within one SQL statement or a With block is beyond me,...

    It's beyond me, too. But then again, I wouldn't even try to do it with a single statement. Avoiding the loop or doing something "set based" does [font="Arial Black"]NOT [/font]mean doing it "within one SQL statement or a Withblock". Multiple set-based passes will be much faster than using a loop for this.

    I honestly don't understand what you mean by that. What would make it set-based? Is there an article I can chew on to get a grasp of this?

    Thanks,

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply