Click here to Skip to main content
15,881,089 members
Search within:


How to insert store procedure value into table
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
stored-procedure
Hi.
I have created the below store procedure in MSSQL2005
It is work fine if execute in server. But I need the result should be into physical table.
I don't know how to do this. Because it is dynamic Pivot store procedure.

Please Help me
Maideen

Below is store procedure

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
    '[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
  dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails 
  ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
  PIVOT ( SUM(Amount)  FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
EXECUTE(@PivotTableSQL)
END
Posted
Updated 15-Jul-13 22:59pm
v2
Add a Solution
Comments
Maciej Los 16-Jul-13 5:12am    
What you mean "physical table"?
Maideen Abdul Kader 16-Jul-13 5:14am    
HiThanks
MSSQL database table.
maideen

3 solutions

What you can do is.. you can create a table Where you want to store the data.... After Creating the Table....
Change your Procedure like this...
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
Delete from CreatedTable -- To Remove the Previous Values.
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
    '[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
  dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails 
  ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
  PIVOT ( SUM(Amount)  FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
Insert into CreatedTable(Column1,Column1,Column1,..Columnn)
EXECUTE(@PivotTableSQL)
Select 'Succesfully Inserted into the Table"
END

U can Export to Excel Sheet from the CreatedTable....Now For Exporting to Excel Sheet u can use this...
SQL
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from CreatedTable 
-- Assuming u already have testing sheet in D Drive

For More information...
[Check this link]

Version 2:
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
    '[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT * into TableName FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
  dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails 
  ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
  PIVOT ( SUM(Amount)  FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '

EXECUTE(@PivotTableSQL)
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName$]') select * from TableName 
Drop Table TableName
Select 'Succesfully Exported to Excel'
END


Hope this Helps.....
 
Share this answer
 
Posted
Updated 16-Jul-13 19:15pm
v7
Comments
Maideen Abdul Kader 16-Jul-13 8:17am    
Mr.Raja sekhar
Thanks for reply
Thing is we do not know the number of column. Because it is dynamic pivot, It will create according to data in table.
Example:
+ @PivotColumnHeaders + is based on data input by user. It will increase. So I need to export directly to excel or need dynamic table.
Is it possible for dynamic table? Increase in column based on user input. Like category. Category can be increased at any level. This is my problem now. Pls

Pls

Maideen
Raja Sekhar S 17-Jul-13 1:16am    
Check the Solution... I Have Updated it.... make sure all the columns are Named....
Maideen Abdul Kader 17-Jul-13 9:02am    
Thanks Mr.Raja sekher
Finally got it. Thanks. can create table.

But cannot export into excel. The error is


"OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Procedure usp_pivot_by_RCVD_Dynamic, Line 16
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Maideen Abdul Kader 17-Jul-13 9:18am    
Mr.Raja Sekhar.
I have tried to select data between two date. But it shows the error
"Must declare the scalar variable "@FDate".

I have inserted the line and i have declare the variable

ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
@FDate AS DATETIME,
@TDate AS DATETIME



SELECT * into tbl_RCVDfor_Rajan FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails
ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor

WHERE dbo.vwRCHeadDetails.RCDate >=@FDate and dbo.vwRCHeadDetails.RCDate <=@TDate

) AS PivotData
PIVOT ( SUM(Amount) FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '

Pls Advise me where is my wrong.
maideen
Raja Sekhar S 18-Jul-13 1:05am    
In the Place of Sheet1 or SheetName Type the Sheet Name of the Excel......
Like In a Excel File Testing.xls there will be many sheets like sheet1,sheet2 so on... so give the name of the sheet to which u want to export..U can use Where dbo.vwRCHeadDetails.RCDate Between @Fdate and @TDate...(Assuming @Tdate is Greater than @Fdate).. Refer the Link Which i provided in the Solution.....
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
    '[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
  dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails 
  ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
  PIVOT ( SUM(Amount)  FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
EXECUTE(@PivotTableSQL)

END


U need to call this stored procedure seperately to obtain the dynamic pivot result
SQL
SELECT *
INTO #Jobs
FROM OPENROWSET('SQLNCLI', 'server=SERVERNAMEINSTANCENAME;trusted_connection=yes', 
'set fmtonly off exec dbo.usp_pivot_by_RCVD_Dynamic')


For reference insert-stored-procedure-results-into-table[^]
 
Share this answer
 
Posted
Comments
Maideen Abdul Kader 17-Jul-13 8:48am    
Mr.Karthik
Thanks for reply
I have error in export to excel. I have tried in admin privilege also. The error is
Cannot process the object "set fmtonly off exec dbo.usp_pivot_by_RCVD_Dynamic".
The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or
the current user does not have permissions on that object.

Maideen
Maideen Abdul Kader 17-Jul-13 9:08am    
Thank you Mr.Karthik.
But cannot export. There is an error in execution.....

annot process the object "set fmtonly off exec dbo.usp_pivot_by_RCVD_Dynamic".
The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or
the current user does not have permissions on that object.

Any solution....
Try this,

I will another solution if this not work for u!!!

SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_pivot_by_RCVD_Dynamic]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = COALESCE(@PivotColumnHeaders + ',[' + CAST(RCVDFor AS VARCHAR) + ']',
    '[' + CAST(RCVDFor AS VARCHAR)+ ']' )FROM dbo.vw_PIVOT_RCVD_DISTINCT
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
  SELECT * FROM (SELECT dbo.vwRCHeadDetails.RCDate,dbo.vwRCHeadDetails.CourseCode,dbo.vw_PIVOT_RCVD_DISTINCT.RCVDFor,
  dbo.vwRCHeadDetails.Amount FROM dbo.vw_PIVOT_RCVD_DISTINCT INNER JOIN vwRCHeadDetails 
  ON dbo.vw_PIVOT_RCVD_DISTINCT.rcvdfor = dbo.vwRCHeadDetails.rcvdfor) AS PivotData
  PIVOT ( SUM(Amount)  FOR RCVDFor IN (' + @PivotColumnHeaders + ' )) AS PivotTable '
INSERT INTO #tempTable (RCDate,CourseCode,RCVDFor,Amount)
EXECUTE(@PivotTableSQL)

/* SELECT from TempTable */
SELECT *
FROM #tempTable

END

Reference: insert-values-of-stored-procedure-in-table
 
Share this answer
 
Posted
Updated 16-Jul-13 0:09am
v3
Comments
Maideen Abdul Kader 16-Jul-13 5:48am    
Mr.Karthik
thanks for quick reply. I have executed. No error. But how can i see the data in the temptable. the whole idea is to export the result into excel sheet. If it is in table, i can export. I think you understand my issue. Pls

Pls
Maideen
[no name] 16-Jul-13 6:11am    
I have improved the solution at the end. plz check.

If u execute the stored procedure (EXEC usp_pivot_by_RCVD_Dynamic), result will be obtained.
Accept the answer if it is helpful!
Maideen Abdul Kader 16-Jul-13 8:45am    
Mr.Karthik
error Invalid object name '#tempTable'.
It is dynamic pivot , It will increase based on user input data.
Pls help me
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
SQL Server stored procedure insert statement using dynamic table name
How do I write a stored procedure that get table name and a record anf insert record into the table
How to insert data in one column using stored procedure (devexpress)
store procedur for insert value problem
how to insert values in multiple table through single store procedure
how to insert 1-9999 values table in stored procedure
Insert values in database table using stored procedure and asp.net?
Need to create temporary table from department table using stored procedure
how to insert values from a stored procedure to one table
Convert Stored Procedure into C#

Advertise
Privacy
Cookies
Terms of Use
Last Updated 17 Jul 2013
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web01 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900