Professional Web Applications Themes

cursor with insert/select - Microsoft SQL / MS SQL Server

I have a cursor that inserts rows in a table. Simple enough stuff, but I'm a little confused by something that's happening. Instead of the sproc finishing and giving me the normal "the command completed successfully", I'm getting 2 resultsets back, with the contents of the rows I'm inserting into a table inside of my cursor. I can't figure out why it's returning these rows to me?! The portion of my sproc with the cursor is below. Can anyone see why the records are being returned to me, instead of simply inserting them into the table? Thanks in advance. Andre ...

  1. #1

    Default cursor with insert/select

    I have a cursor that inserts rows in a table. Simple enough stuff, but I'm
    a little confused by something that's happening. Instead of the sproc
    finishing and giving me the normal "the command completed successfully", I'm
    getting 2 resultsets back, with the contents of the rows I'm inserting into
    a table inside of my cursor. I can't figure out why it's returning these
    rows to me?!

    The portion of my sproc with the cursor is below. Can anyone see why the
    records are being returned to me, instead of simply inserting them into the
    table?

    Thanks in advance. Andre




    -- begin cursor
    DECLARE AddRecord CURSOR
    FOR SELECT DISTINCT CubeID, RewardsProcessingParametersIDString FROM #Temp
    OPEN AddRecord

    FETCH NEXT FROM AddRecord INTO nCubeID,
    strRewardsProcessingParametersIDString

    WHILE FETCH_STATUS = 0
    BEGIN

    -- get next JobNumber
    EXEC nJobNumber = IQP_GetNextJobNumber

    -- insert record into Jobs, scheduling fact table refresh
    INSERT dbo.Jobs (
    JobNumber,
    JobSequence,
    JobType,
    [Description],
    Owner,
    CreatedOn,
    ScheduledFor,
    Parameters,
    SQLJob,
    SQLJobType)
    SELECT
    JobNumber = nJobNumber,
    JobSequence = 0,
    JobType = 1,
    [Description] = 'OLAP Fact Table Refresh',
    Owner = strUserName,
    CreatedOn = GETDATE(),
    ScheduledFor = GETDATE(),
    Parameters = 'EXEC dbo.IQP_OLAPFact' + CAST(T.CubeID AS VARCHAR) +
    'Refresh dtPayrollDate=''' + CONVERT(VARCHAR, dtPayrollDate, 121) + '''
    ,strRewardsProcessingParametersID=''' +
    CAST(T.RewardsProcessingParametersIDString AS VARCHAR) + ''' ,nCubeID=' +
    CAST(T.CubeID AS VARCHAR),
    SQLJob = 1,
    SQLJobType = 'TSQL'
    FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString FROM
    #Temp ) T
    WHERE CubeID = nCubeID
    AND RewardsProcessingParametersIDString =
    strRewardsProcessingParametersIDString


    -- insert record into Jobs, scheduling cube refresh
    INSERT dbo.Jobs (
    JobNumber,
    JobSequence,
    JobType,
    [Description],
    Owner,
    CreatedOn,
    ScheduledFor,
    Parameters,
    SQLJob,
    SQLJobType)
    SELECT
    JobNumber = nJobNumber,
    JobSequence = 1,
    JobType = 1,
    [Description] = 'OLAP Cube Refresh',
    Owner = strUserName,
    CreatedOn = GETDATE(),
    ScheduledFor = GETDATE(),
    Parameters = 'DTSRUN /S SQL01 /E /NCube' + CAST(T.CubeID AS VARCHAR) +
    'Refresh',
    SQLJob = 1,
    SQLJobType = 'CMDEXEC'
    FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString FROM
    #Temp ) T
    WHERE CubeID = nCubeID
    AND RewardsProcessingParametersIDString =
    strRewardsProcessingParametersIDString

    FETCH NEXT FROM AddRecord INTO nCubeID,
    strRewardsProcessingParametersIDString

    END

    DEALLOCATE AddRecord




    Andre Guest

  2. #2

    Default Re: cursor with insert/select

    Der...I just remembered that I was playing with my Jobs table and had put an
    insert trigger on it. It was doing a "select * from inserted", thus the
    reason why the records were being returned!

    Sorry to bother you guys...

    Andre

    "Andre" <AndreGetsEnoughSPAMnospam.com> wrote in message
    news:eU0VVLYRDHA.2148TK2MSFTNGP11.phx.gbl...
    > it's already there.
    >
    >
    > "oj" <nospam_ojngohome.com> wrote in message
    > news:eV0lPvXRDHA.2144TK2MSFTNGP11.phx.gbl...
    > > Add "set nocount on" to the beginning of your sproc should do it.
    > >
    > > --
    > > -oj
    > > RAC v2.2 & QALite!
    > > [url]http://www.rac4sql.net[/url]
    > >
    > >
    > > "Andre" <AndreGetsEnoughSPAMnospam.com> wrote in message
    > > news:#m4F6fXRDHA.2020TK2MSFTNGP11.phx.gbl...
    > > > I have a cursor that inserts rows in a table. Simple enough stuff,
    but
    > > I'm
    > > > a little confused by something that's happening. Instead of the sproc
    > > > finishing and giving me the normal "the command completed
    successfully",
    > > I'm
    > > > getting 2 resultsets back, with the contents of the rows I'm inserting
    > > into
    > > > a table inside of my cursor. I can't figure out why it's returning
    > these
    > > > rows to me?!
    > > >
    > > > The portion of my sproc with the cursor is below. Can anyone see why
    > the
    > > > records are being returned to me, instead of simply inserting them
    into
    > > the
    > > > table?
    > > >
    > > > Thanks in advance. Andre
    > > >
    > > >
    > > >
    > > >
    > > > -- begin cursor
    > > > DECLARE AddRecord CURSOR
    > > > FOR SELECT DISTINCT CubeID, RewardsProcessingParametersIDString FROM
    > > #Temp
    > > > OPEN AddRecord
    > > >
    > > > FETCH NEXT FROM AddRecord INTO nCubeID,
    > > > strRewardsProcessingParametersIDString
    > > >
    > > > WHILE FETCH_STATUS = 0
    > > > BEGIN
    > > >
    > > > -- get next JobNumber
    > > > EXEC nJobNumber = IQP_GetNextJobNumber
    > > >
    > > > -- insert record into Jobs, scheduling fact table refresh
    > > > INSERT dbo.Jobs (
    > > > JobNumber,
    > > > JobSequence,
    > > > JobType,
    > > > [Description],
    > > > Owner,
    > > > CreatedOn,
    > > > ScheduledFor,
    > > > Parameters,
    > > > SQLJob,
    > > > SQLJobType)
    > > > SELECT
    > > > JobNumber = nJobNumber,
    > > > JobSequence = 0,
    > > > JobType = 1,
    > > > [Description] = 'OLAP Fact Table Refresh',
    > > > Owner = strUserName,
    > > > CreatedOn = GETDATE(),
    > > > ScheduledFor = GETDATE(),
    > > > Parameters = 'EXEC dbo.IQP_OLAPFact' + CAST(T.CubeID AS VARCHAR) +
    > > > 'Refresh dtPayrollDate=''' + CONVERT(VARCHAR, dtPayrollDate, 121) +
    > '''
    > > > ,strRewardsProcessingParametersID=''' +
    > > > CAST(T.RewardsProcessingParametersIDString AS VARCHAR) + '''
    ,nCubeID='
    > +
    > > > CAST(T.CubeID AS VARCHAR),
    > > > SQLJob = 1,
    > > > SQLJobType = 'TSQL'
    > > > FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString
    > FROM
    > > > #Temp ) T
    > > > WHERE CubeID = nCubeID
    > > > AND RewardsProcessingParametersIDString =
    > > > strRewardsProcessingParametersIDString
    > > >
    > > >
    > > > -- insert record into Jobs, scheduling cube refresh
    > > > INSERT dbo.Jobs (
    > > > JobNumber,
    > > > JobSequence,
    > > > JobType,
    > > > [Description],
    > > > Owner,
    > > > CreatedOn,
    > > > ScheduledFor,
    > > > Parameters,
    > > > SQLJob,
    > > > SQLJobType)
    > > > SELECT
    > > > JobNumber = nJobNumber,
    > > > JobSequence = 1,
    > > > JobType = 1,
    > > > [Description] = 'OLAP Cube Refresh',
    > > > Owner = strUserName,
    > > > CreatedOn = GETDATE(),
    > > > ScheduledFor = GETDATE(),
    > > > Parameters = 'DTSRUN /S SQL01 /E /NCube' + CAST(T.CubeID AS
    VARCHAR)
    > +
    > > > 'Refresh',
    > > > SQLJob = 1,
    > > > SQLJobType = 'CMDEXEC'
    > > > FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString
    > FROM
    > > > #Temp ) T
    > > > WHERE CubeID = nCubeID
    > > > AND RewardsProcessingParametersIDString =
    > > > strRewardsProcessingParametersIDString
    > > >
    > > > FETCH NEXT FROM AddRecord INTO nCubeID,
    > > > strRewardsProcessingParametersIDString
    > > >
    > > > END
    > > >
    > > > DEALLOCATE AddRecord
    > > >
    > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Andre Guest

Similar Threads

  1. What's faster - loop for insert or insert...select.
    By mr. modus in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 10th, 01:57 AM
  2. SELECT and INSERT
    By McKirahan in forum ASP Database
    Replies: 4
    Last Post: August 2nd, 05:45 PM
  3. AICS: select text on an unlocked path to insert some chars and lock cursor shows
    By envelope_the_frontcat@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 1
    Last Post: March 3rd, 11:58 PM
  4. Replies: 1
    Last Post: August 24th, 03:45 PM
  5. Insert from Select
    By RPhillips@ce-a.com in forum Informix
    Replies: 6
    Last Post: July 30th, 10:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139