reset indentity field in table variable not in table

  • I'm using table variable in place of cursor to increase the performance.

    There are two table variables names : @EmpMaster and @ECFMaster in place of two cursors.

    I'm doing some process from getting data from first table variable @EmpMaster and using that data inside second table variable @ECFMaster.. means like cursor inside another cursor.

    I'm using identity field in table variables to loop through data.

    Now, inner table variable will load everytime from outer table variable, So every time I have to start inner table variable value from one for identity field. But I can't truncate,drop table variable or reset identity field of table variable. So what is the way to do that ? Please give proper help using example in "SQL SERVER 2000."

    set @RowCnt=1

    declare @EmpMaster table (

    Rownum int identity(1,1),

    EmpID int,

    EmpName varchar(50),

    Status varchar(20),

    Termination_date smalldatetime,

    agentid varchar(20))

    declare @ECFMaster table (

    RowID int identity(1,1),

    ECFID int,

    Effective_date smalldatetime)

    insert @EmpMaster

    SELECT distinct Emp_Id,real_fname+', '+real_lname+'('+cast(Emp_ID as varchar(20))+')' as EmpName,Status,Termination_Date,NT_Domain

    FROM ERRISIndia.Effectivereports.EmployeeMaster

    WHERE (Designation='Voice Agent' OR Designation='Mentor'OR Designation like'%agent%')

    and Status='Active'

    select @MaxRows=@@rowcount

    WHILE @RowCnt <=@MaxRows

    BEGIN

    select @Emp_Id=EmpID,

    @Emp_Name=EmpName,

    @status=status,

    @Terdate=Termination_date,

    @AgentID=agentid

    from @EmpMaster

    where Rownum=@RowCnt

    delete from @ECFMaster

    set @RowCount=1

    insert @ECFMaster

    SELECT ECF_ID,corrected_Effective_Date FROM Effectivereports.Effectivereports.ECFIndiaMaster WHERE Emp_ID=@Emp_ID

    set @TotalRows=@@rowcount

    WHILE @RowCount <=@TotalRows

    BEGIN

    select @ECF_Id=ECFID,

    @EFFdate=Effective_date

    from @ECFMaster

    where RowID=@RowCount

    ------- Doing some process inside

    set @RowCount=@RowCount+1

    END

    delete from @EmpMaster where Rownum=@RowCnt

    set @RowCnt=@RowCnt+1

    END

  • You need to post in the appropriate forums for your question. I've moved this to T-SQL, 2005, but if it's not the right place, please let me know.

  • If you are using SQL 2005, can you use row_number() instead of the identity column?

    Something like:

    INSERT@ECFMaster (RowID , ECFID , Effective)

    SELECTROW_NUMBER() OVER (ORDER BY ECF_ID,corrected_Effective_Date ),

    ECF_ID,

    corrected_Effective_Date

    FROMEffectivereports.Effectivereports.ECFIndiaMaster

    WHEREEmp_ID=@Emp_ID

  • If you're looking to improve your performance - you need to ditch the inner cursor. Selecting one row at a time, and process updates one row at a time MIGHT help with performance, but is actually more likelt to be LESS efficient than staying with the straight cursor. The retrieve and delete and re-insert is likely to make the table variable WORSE than the cursor.

    You're essentially replacing a cursor with another cursor. Just because you aren't actually using the word cursor doesn't mean your process isn't doing the same thing.

    In order to truly improve performance, you really want to work on removing BOTH loops. So that the process affects bunches of records at a time, not one at a time. Come on, fess up - what's behind the ------- Doing some process inside ?

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • The only way I know to do what you're asking is to move the second table variable to a separate proc, and call that proc with input parameters from the first table.

    But, as pointed out, that's just a way to use a cursor inside a cursor without calling it that. To misquote The Bard, "A cesspool by any other name would smell ...."

    The better bet is to try to find a way to do the whole thing as a set, instead of row by row.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • ditto Gsquared.

    If you must use a cursor, use the SQL Server one, do NOT write your own in TSQL. I've never see a "hand written" cursor come anywhere close to the performance of the native cursor support. As a matter of fact, I've was a "hero" for changing a hand written cursor to a native one...shaved a process from a couple hours to 10 minutes... . The developer was told to NEVER use a cursor - he followed the rules given to him and wrote his own, then came to us for performance assistance.

    Should have been told to avoid cursors if possible.

    jg

Viewing 6 posts - 1 through 5 (of 5 total)

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