如何在新表中保存存储过程结果 [英] How to save stored procedure result in new table
问题描述
我希望将存储过程结果保存在新表中
这是我的程序
< pre lang =SQL> SELECT
PaymentTracker1。*
, convert ( varchar ( 20 ),DATEADD(m,N,StartDate), 110 ) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
FROM
PaymentTracker1
INNER JOIN 数字 DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate)> = Numbers.N
其中 datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate())和 datepart(yy,DATEADD(y,N,StartDat e))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N
如何将此程序的结果值保存到新表中
你可以试试SELECT INTO。这将为您创建一个新表,如果您希望通过在表名前加上井号(#),则可以是临时表。
SELECT *
INTO #tempTableName
FROM TableName
您的查询将是
SELECT PaymentTracker1。*, convert ( varchar ( 20 ),DATEADD(m,N,StartDate), 110 ) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
INTO #tempResult
< span class =code-keyword> FROM PaymentTracker1
INNER JOIN 数字 DATEDIFF(m,PaymentTr acker1.StartDate,PaymentTracker1.EndDate)> = Numbers.N
其中 datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm ,getdate())和 datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N
如果你想将它存储在永久表中,那么首先你必须定义一个具有完全相同列的表,你在查询中选择使用上面查询中的这个表。
< blockquote>创建临时表
创建 TABLE #tmp
(
COL1 INT ,
COL2 INT
)
写入insert语句并执行存储过程
INSERT INTO #tmp
Exec SpName ' Params'
试试这个
INSERT NEW_TABLE
EXEC SPNAME
i want save my stored procedure result in new table
this is my procedure
SELECT
PaymentTracker1.*
, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate
,(Numbers.N * MonthlyPay) AS DueAmount
FROM
PaymentTracker1
INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N
where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate())
ORDER BY
PaymentTracker1.Name
,Numbers.N
how to save resulted value of this procedure into new table
you can try SELECT INTO. This will create a new table for you, which can be temporary if you want by prefixing the table name with a pound sign (#).
SELECT * INTO #tempTableName FROM TableName
your query will be
SELECT PaymentTracker1.*, convert(varchar(20),DATEADD(m,N,StartDate),110) AS DueDate ,(Numbers.N * MonthlyPay) AS DueAmount INTO #tempResult FROM PaymentTracker1 INNER JOIN Numbers on DATEDIFF(m,PaymentTracker1.StartDate,PaymentTracker1.EndDate) >= Numbers.N where datepart(mm,DATEADD(mm,N,StartDate))= datepart(mm,getdate()) and datepart(yy,DATEADD(y,N,StartDate))= datepart(yy,getdate()) ORDER BY PaymentTracker1.Name ,Numbers.N
If you want to store it in permanent table then first you have to define a table with exactly same columns which are you selecting in query the use this table in above query.
create a temp table
CREATE TABLE #tmp ( COL1 INT, COL2 INT )
write insert statement and execute the stored procedure
INSERT INTO #tmp Exec SpName 'Params'
Try this
INSERT NEW_TABLE EXEC SPNAME
这篇关于如何在新表中保存存储过程结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!