insert value of a stored procedure into a temp table

Hi there,
I took this code from a stored procedure.
declare curM cursor for 
SELECT * FROM #MTUPorts
OPEN curM
Fetch next from curM Into @MTUID, @PortID
while @@FETCH_STATUS = 0 begin

insert into #tblConsumption
EXEC starNG_CalcConsumption @MTUID, 
	@port =@PortID, 
	@dateStart = @dtStart, 
	@dateEnd =@dtEnd
	
Fetch next from curM Into @MTUID, @PortID	
	
end
close curM
deallocate curM

Open in new window


What I need to do is to also include in the insert the @MTUID and @PortID how can I do this?
Thanks
COHFLAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What starNG_CalcConsumption returns?
And what is the table #tblConsumption definition?
COHFLAuthor Commented:
starNG_CalcConsumption  returns this values
[ID]                        INT IDENTITY(1,1) PRIMARY KEY,
      [ReadingDate]            DateTime,
      [RawConsumption]      Decimal(20,6),
      [Consumption]            Decimal(20,6),
      scalar                        REAL,
      Units                        INT,
      Utility                        NVARCHAR(20),
      AccountNum                  NVARCHAR(20),
      SerialNumber            NVARCHAR(20)

CREATE TABLE #tblConsumption (
      [ID]                        INT IDENTITY(1,1) PRIMARY KEY,
      [ReadingDate]            DateTime,
      [RawConsumption]      Decimal(20,6),
      [Consumption]            Decimal(20,6),
      scalar                        REAL,
      Units                        INT,
      Utility                        NVARCHAR(20),
      AccountNum                  NVARCHAR(20),
      SerialNumber            NVARCHAR(20),
MTU INT,
PORT INT
)
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, you are already passing the  @MTUID and @PortID as parameters so in the starNG_CalcConsumption  just add those variables in the SELECT list:
SELECT [ReadingDate],
       [RawConsumption],
       [Consumption],
       scalar,
       Units,
       Utility,
       AccountNum,
       SerialNumber,
  @MTUID,
  @PortID 
FROM .... 

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

COHFLAuthor Commented:
By doing this I will have
INSERT INTO #...
SELECT ...
FROM ...
EXEC ..

and this throws an error "Incorrect syntax near the keyword 'EXEC'."
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, your previous code wasn't working at all, then.
Vitor MontalvãoMSSQL Senior EngineerCommented:
For you work with the results returned by a SP you need to enable the AD HOC DISTRIBUTED QUERIES and use the OPENROWSET function:
-------------- This part you'll only need to run once ------------------
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OVERRIDE
GO
------------------------------------------------------------------------

INSERT INTO #tblConsumption 
FROM OPENROWSET('SQLNCLI', 'Server=ServerName;Trusted_Connection=yes;',
     'EXEC starNG_CalcConsumption ...')

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alfred A.Commented:
You could try the following if you don't want to use OPENROWSET or if you don't want to just add the MTU and PORT as return values for starNG_CalcConsumption stored procedure:

1.  Create a temp table for containing the output of starNG_CalcConsumption.   I will name it #tblConsumption_Partial as an example.

CREATE TABLE #tblConsumption_Partial (
      [ID]                        INT IDENTITY(1,1) PRIMARY KEY,
      [ReadingDate]            DateTime,
      [RawConsumption]      Decimal(20,6),
      [Consumption]            Decimal(20,6),
      scalar                        REAL,
      Units                        INT,
      Utility                        NVARCHAR(20),
      AccountNum                  NVARCHAR(20),
      SerialNumber            NVARCHAR(20)
)

2.   Insert stored procedure output

INSERT INTO #tblConsumption_Partial
EXEC starNG_CalcConsumption @MTUID,
      @port =@PortID,
      @dateStart = @dtStart,
      @dateEnd =@dtEnd

3.  After executing no. 2, insert it again to the main temp table within the WHILE in your cursor.   This should do it.

INSERT INTO #tblConsumption
SELECT T.*, @MTUID, @PortID FROM #tblConsumption_Partial T


Note:   if I have a choice, I would just update the starNG_CalcConsumption stored procedure to include the @MTUID and @PortID as output.   This would at least avoid creating another temp table.

I hope this helps.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.