Sql server - 如何将单行插入临时表? [英] Sql server - how to insert single row into temporary table?
本文介绍了Sql server - 如何将单行插入临时表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有两个临时表,当我在一个表中循环并从中获取一些值时,在这个循环中我需要将新行插入到另一个临时表中.这可能吗.这是我的sql代码和错误信息:
I have two temporary table, when i do cycle through one table and i get some values from it, in this cycle I need insert new row into another temporary table. Is this possible. Here is my sql code and error information:
Alter PROCEDURE ProfitReportQ_Search_WithSub
(@DateFrom datetime,
@DateTo datetime,
@DateActive bit,
@UserID int,
@ItemGroupIDValues nvarchar(max)
)
AS
BEGIN
CREATE TABLE #tmp(ItemGroupID int, ItemGroupName nvarchar(250), Manager nvarchar(250), AllQuantity int, AllSumPrice AllSumPrice, AllSumPriceWithVAT decimal(18,4), Profit decimal(18,4))
CREATE TABLE #tmp2(Manager nvarchar(250), AllQuantity int, AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4), Profit decimal(18,4), ItemGroupNameRoot nvarchar(250))
INSERT INTO #tmp
EXEC ProfitReportQ_Search @DateFrom, @DateTo, @DateActive, @UserID, @ItemGroupIDValues
DECLARE @ItemGroupID int
DECLARE @ItemGroupName nvarchar(250)
DECLARE @Manager nvarchar(250)
DECLARE @AllQuantity int
DECLARE @AllSumPrice decimal(18,4)
DECLARE @AllSumPriceWithVAT decimal(18,4)
DECLARE @Profit decimal(18,4)
DECLARE @ItemGroupNameRoot nvarchar(250)
DECLARE @count int
SET @count = (SELECT COUNT(*) FROM #tmp)
WHILE (@count <> 0)
BEGIN
SELECT TOP (1) @ItemGroupID = ItemGroupID, @ItemGroupName = ItemGroupName, @Manager = Manager, @AllQuantity = AllQuantity, @AllSumPrice = AllSumPrice, @AllSumPriceWithVAT = AllSumPriceWithVAT, @Profit = Profit FROM #tmp
DELETE #tmp WHERE ItemGroupID = ItemGroupID AND ItemGroupName = @ItemGroupName AND Manager = @Manager AND AllQuantity = @AllQuantity AND AllSumPrice = @AllSumPrice AND AllSumPriceWithVAT = @AllSumPriceWithVAT AND Profit = @Profit
INSERT INTO #tmp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
VALUES (@Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, EXEC ItemGroup_GetRootWithRecurse @ItemGroupID)
END
SELECT ItemGroupNameRoot, Manager, SUM(AllQuantity) AS AllQuantity, SUM(AllSumPrice) AS AllSumPrice,
SUM(AllSumPriceWithVAT) AS AllSumPriceWithVAT, SUM(Profit) AS Profit
FROM #tmp2
GROUP BY ItemGroupNameRoot, Manager
DELETE #tmp
DELETE #tmp2
END
GO
这行有问题:
INSERT INTO #tmp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
VALUES (@Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, EXEC ItemGroup_GetRootWithRecurse @ItemGroupID)
错误:
关键字EXEC"附近的语法不正确.')' 附近的语法不正确.
Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.
一些想法?
推荐答案
当它在另一个 SP 中执行时,我无法将这些值放入 #temp2 中.我使用了三个临时表,如下所示:
It was not possible for me to put these values into #temp2 when it was execute in another SP. I worked out with three temporary tables, like this:
开始
CREATE TABLE #tmp(ItemGroupID int, ItemGroupName nvarchar(250),
Manager nvarchar(250), AllQuantity int,
AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4),
Profit decimal(18,4), ID int
)
CREATE TABLE #tmp2(Manager nvarchar(250), AllQuantity int,
AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4),
Profit decimal(18,4), ItemGroupNameRoot nvarchar(250)
)
CREATE TABLE #tmp3(ItemGroupNameRoot nvarchar(250))
INSERT INTO #tmp
EXEC ProfitReportQ_Search_v2 @DateFrom, @DateTo, @DateActive, @UserID, @ItemGroupIDValues
DECLARE @ID int
DECLARE @ItemGroupID int
DECLARE @ItemGroupName nvarchar(250)
DECLARE @Manager nvarchar(250)
DECLARE @AllQuantity int
DECLARE @AllSumPrice decimal(18,4)
DECLARE @AllSumPriceWithVAT decimal(18,4)
DECLARE @Profit decimal(18,4)
DECLARE @ItemGroupNameRoot nvarchar(250)
DECLARE @count int
SET @count = (SELECT COUNT(*) FROM #tmp)
WHILE (@count <> 0)
BEGIN
SELECT TOP (1) @ItemGroupID = ItemGroupID, @ItemGroupName = ItemGroupName, @Manager = Manager, @AllQuantity = AllQuantity, @AllSumPrice = AllSumPrice, @AllSumPriceWithVAT = AllSumPriceWithVAT, @Profit = Profit, @ID = ID
FROM #tmp
DELETE #tmp WHERE ID = @ID
INSERT INTO #tmp3 EXEC ItemGroup_GetRootWithRecurse_ForProfitReport @ItemGroupID
SELECT TOP(1)@ItemGroupNameRoot = ItemGroupNameRoot
FROM #tmp3
INSERT INTO #tmp2 SELECT @Manager, @AllQuantity,
@AllSumPrice, @AllSumPriceWithVAT,
@Profit, @ItemGroupNameRoot
DELETE #tmp3
SET @count = (SELECT COUNT(*) FROM #tmp)
END
DELETE #tmp
SELECT ItemGroupNameRoot, Manager, SUM(AllQuantity) AS AllQuantity, SUM(AllSumPrice) AS AllSumPrice,
SUM(AllSumPriceWithVAT) AS AllSumPriceWithVAT, SUM(Profit) AS Profit
FROM #tmp2
GROUP BY ItemGroupNameRoot, Manager
DELETE #tmp2
结束去
这篇关于Sql server - 如何将单行插入临时表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文