如何将存储过程值插入表中 [英] How to insert store procedure value into table

问题描述

嗨。

我在MSSQL2005中创建了以下存储过程

如果在服务器中执行，它可以正常工作。但是我需要把结果放到物理表中。

我不知道怎么做。因为它是动态的Pivot商店程序。



请帮帮我

Maideen



以下是商店程序

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

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

推荐答案

你能做什么你可以创建一个表来存储数据。 ...创建表格后......

像这样更改你的程序......

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...

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

您可以从CreatedTable导出到Excel工作表....现在要导出到Excel工作表，您可以使用它。 ..

U can Export to Excel Sheet from the CreatedTable....Now For Exporting to Excel Sheet u can use this...

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;', 
    'SELECT * FROM [SheetName

' ）选择 * 来自 CreatedTable
- 假设你已经在D Drive中有测试表

') select * from CreatedTable -- Assuming u already have testing sheet in D Drive

更多信息......

[查看此链接]



版本2：

For More information...
[Check this link]

Version 2:

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

'）选择 * from TableName
Drop 表 TableName
选择 ' 成功导出到Excel'
END

') select * from TableName Drop Table TableName Select 'Succesfully Exported to Excel' END

希望这有助于.....

Hope this Helps.....

这篇关于如何将存储过程值插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！