Post reply

Cursor Help Please - well...I think I need a cursor.

  • October 12, 2004 at 11:42 am

    #59624

    I am completely unfamiliar with cursors but I think I need one now.  I am going through BOL...which is how I found info on cursors, but I am still a bit unclear.

    I am trying to insert values into a table with a select on another table.  However, one of the values needs to autoincrement a key id concatenated with a string.

    Bascially:

    INSERT INTO EmpLicns (ElcEEID, ElcLicenseID, ElcNumber, ElcSystemID)

    select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04, 'User'+1 as ElcSystemID

    FROM EmpComp

    My problem is that ElcSystemID needs to say User1, User2, User3, etc.  Can someone help?

    Thanks

    Ryan

  • October 12, 2004 at 2:17 pm

    #526407

    you could always stage into a temp table:

    SELECT IDENTITY( INT, 1, 1) as seq

           ,* --column list

      INTO #stage

      FROM sourcetable

    INSERT INTO destinationtable( column list)

       SELECT ..., 'user'+CONVERT(VARCHAR(10), seq)

          FROM #stage

     

  • October 12, 2004 at 2:26 pm

    #526410

    select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04,

    ElcSystemID='user' + cast(((select count(*) from EmpComp as emp where emp.EecEEID<EmpComp.EecEEID) +1) as varchar)

    FROM EmpComp

    order by EecEEID

  • October 12, 2004 at 2:27 pm

    #526412

    without temp tables, make sure it is sorted:

    INSERT INTO EmpLicns (ElcEEID, ElcLicenseID, ElcNumber, ElcSystemID)

    select EecEEID, 'DRIVER' as ElcLicenseID, EecUDField04,

    ElcSystemID='user' + cast(((select count(*) from EmpComp as emp where emp.EecEEID<EmpComp.EecEEID) +1) as varchar)

    FROM EmpComp

    order by EecEEID

     

  • October 13, 2004 at 6:54 am

    #526502

    Thanks to you both.  Both solutions worked properly.

    Ryan

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

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