Sql server - 如何将单行插入临时表?
我有两个临时表,当我循环访问一个表并从中获取一些值时,在这个循环中我需要将新行插入到另一个临时表中。 这可能吗。 这是我的sql代码和错误信息:
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”附近的语法不正确。 ')' 附近的语法不正确。
一些想法?
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
There is a problem with this lines:
INSERT INTO #tmp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
VALUES (@Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, EXEC ItemGroup_GetRootWithRecurse @ItemGroupID)
Error:
Incorrect syntax near the keyword 'EXEC'.
Incorrect syntax near ')'.
Some ideas?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
当 #temp2 在另一个 SP 中执行时,我不可能将这些值放入 #temp2 中。
我使用了三个临时表,如下所示:
BEGIN
END
去
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:
BEGIN
END
GO
您似乎正在尝试将以下结果插入到 #tmp2 的 ItemGroupNameRoot 列中:
您确实无法执行存储过程并以这种方式使用结果。 相反,我建议用用户定义的函数替换 ItemGroup_GetRootWithRecurse 存储过程。 它可能看起来像这样:
您可以以与存储过程几乎相同的方式调用该函数。 最大的区别是您删除了 EXEC 语句,因此调用如下所示:
我希望这会有所帮助。
It appears that you are trying to insert the result of the following into a #tmp2's ItemGroupNameRoot column:
You really can't execute stored procedures and use results that way. Instead, I suggest replacing the ItemGroup_GetRootWithRecurse stored procedure with a user defined function. It might looking something like this:
You would call the function nearly the same way as the stored procedure. The big difference is you drop the EXEC statement so the call looks like this:
I hope this helps.
简短回答:
您可以与其调用的存储过程共享在存储过程中创建的临时表。 您可以尝试使用此技术插入 ItemGroup_GetRootWithRecurse 内的#temp2。
长答案:
这可能会有所帮助:如何在存储过程之间共享数据。 您可以阅读整篇文章,但是有关在过程之间共享临时表的链接部分可以解决问题。
您可以将 @Manager、@AllQuantity、@AllSumPrice、@AllSumPriceWithVAT、@Profit 以及 @ItemGroupID 传递到 ItemGroup_GetRootWithRecurse 中,然后在其中插入 #tmp2,其中包含以下内容:
这段代码可能有点不对劲,因为我不这样做没有所有详细信息,但应该很接近。
short answer:
You can share a temp table created in a stored procedure with a stored procedure that it calls. You could try to use this technique to insert into #temp2 within ItemGroup_GetRootWithRecurse.
long answer:
this may help: How to Share Data Between Stored Procedures. You can read the entire article, but the linked section on sharing temp tables between procedures my do the trick.
you could pass @Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit as well as @ItemGroupID into ItemGroup_GetRootWithRecurse, and within, insert into #tmp2 with something like:
this code may be a little off, since I don't have al the details, but should be close.