Ask a Question related to Microsoft SQL / MS SQL Server, Design and Development.
-
Andre #1
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
-
What's faster - loop for insert or insert...select.
What is faster if I'm moving large numbers of records (anywhere from 10,000 to 300,000 records per archive) from one query to another table? 1)... -
SELECT and INSERT
Is there a way to do the SELECT and INSERT in a single SQL statement in MS-Access? -
AICS: select text on an unlocked path to insert some chars and lock cursor shows
This is very strange: The current layer selected in my document is one which is locked. Then I click on a text on a path item which is on an unlocked... -
Insert - Declare Cursor in Proc wont syntax check - help!
Derek, declarqations must be before any other coomands -
Insert from Select
I'm trying to insert from a select statement but it is giving me an error that I "Cannot modify table or view used in subquery." on my where clause.... -
Andre #2 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" <AndreGetsEnoughSPAM@nospam.com> wrote in message
news:eU0VVLYRDHA.2148@TK2MSFTNGP11.phx.gbl...but> it's already there.
>
>
> "oj" <nospam_ojngo@home.com> wrote in message
> news:eV0lPvXRDHA.2144@TK2MSFTNGP11.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" <AndreGetsEnoughSPAM@nospam.com> wrote in message
> > news:#m4F6fXRDHA.2020@TK2MSFTNGP11.phx.gbl...> > > I have a cursor that inserts rows in a table. Simple enough stuff,successfully",> > I'm> > > a little confused by something that's happening. Instead of the sproc
> > > finishing and giving me the normal "the command completedinto> these> > I'm> > into> > > getting 2 resultsets back, with the contents of the rows I'm inserting> > > a table inside of my cursor. I can't figure out why it's returning> the> > > rows to me?!
> > >
> > > The portion of my sproc with the cursor is below. Can anyone see why> > > records are being returned to me, instead of simply inserting them,@nCubeID='> '''> > the> > #Temp> > > table?
> > >
> > > Thanks in advance. Andre
> > >
> > >
> > >
> > >
> > > -- begin cursor
> > > DECLARE AddRecord CURSOR
> > > FOR SELECT DISTINCT CubeID, RewardsProcessingParametersIDString FROM> > > 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) + '''VARCHAR)> +> FROM> > > CAST(T.CubeID AS VARCHAR),
> > > SQLJob = 1,
> > > SQLJobType = 'TSQL'
> > > FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString> > > #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> +> FROM> > > 'Refresh',
> > > SQLJob = 1,
> > > SQLJobType = 'CMDEXEC'
> > > FROM ( SELECT DISTINCT CubeID, RewardsProcessingParametersIDString>> >> > > #Temp ) T
> > > WHERE CubeID = @nCubeID
> > > AND RewardsProcessingParametersIDString =
> > > @strRewardsProcessingParametersIDString
> > >
> > > FETCH NEXT FROM AddRecord INTO @nCubeID,
> > > @strRewardsProcessingParametersIDString
> > >
> > > END
> > >
> > > DEALLOCATE AddRecord
> > >
> > >
> > >
> > >
> >
>
Andre Guest
Reply With QuoteSimilar Questions and Discussions
Posting Permissions
- You may not post new threads
- You may post replies
- You may not post attachments
- You may not edit your posts
