如何在INSERT语句中使用存储过程中的OUTPUT

Question

我正试图使用INSERT INTO和存储过程将数据从一个表复制到另一个表，并将源表中不存在的一列添加到目标表中，以生成附加列的值。

目标表具有counter字段(整数(，该字段需要为插入的每一行递增(它不是IDENTITY列，递增此计数器由其他代码处理(。

Counter表具有多个表的计数器，需要将Counter Name作为参数传递给存储过程，并使用output作为目标表的附加列。

我有一个存储过程，可以给它传递一个参数(计数器名称，在下面的例子中，计数器名称是"counter_123"(，它的output值与新计数器值相同。

我可以像这样运行存储过程，它运行得很好：

declare @value int
exec GetCounter 'Counter_123', @value output
PRINT @value

在这种情况下，如果计数器为3，则output为4。

以下是我在INSERT INTO:中要做的事情

INSERT INTO Target_Table (col1, col2, col3, uspGetCounter 'Counter_123')
SELECT (val1, val2, val3) 
FROM Source_Table
WHERE ...

返回的错误是"Counter_123"附近的语法错误

我还尝试将存储过程放在值列表中，但这也不起作用。

调用存储过程、向其传递参数并将INSERT INTO查询中的output用作列的值的语法是什么？

以下是存储过程代码供参考：

CREATE PROCEDURE [dbo].[uspGetCounter] 

@CounterName varchar(30) = '', 
@LastValue int OUTPUT
AS
BEGIN
SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
if not exists (select * from Counter where COUNTER_NAME=@CounterName) 
insert Counter (COUNTER_NAME, LAST_VALUE)
values (@CounterName,0)
select @LastValue=LAST_VALUE+1
from Counter (holdlock)
where COUNTER_NAME= @CounterName
update Counter
set LAST_VALUE=@LastValue,
LAST_UPDATED=getdate(),
from Counter
where COUNTER_NAME=@CounterName
COMMIT TRANSACTION

END

Answer 1

假设您真的需要推出自己的序列生成器，因为使用内置序列会容易得多，那么使用当前逻辑，您一次只能插入一行，因为您的存储过程没有提供一次性分配值块的机制。因此，您需要某种循环，可能是一个光标，例如

DECLARE @Counter int, @Val1 int, @Val2 int, @Val3 int;
DECLARE MyCursor CURSOR LOCAL FOR
SELECT val1, val2, val3 
FROM Source_Table
WHERE ...;
WHILE 1 =1 BEGIN
FETCH NEXT FROM MyCursor INTO @Val1, @Val2, @Val3;
IF @@FETCH_STATUS != 0 BREAK;
EXEC uspGetCounter 'Counter_123', @Counter OUTPUT;
INSERT INTO Target_Table (Val1, Val2, Val3, CounterCol)
VALUES (@Val1, @Val2, @Val3, @Counter);
END;

在为需要插入并获得新序列值的每一行运行SP的位置。

如果使用这种方法，我也会修改你的SP如下性能：

-- Attempt an update and assignment in a single statement
UPDATE dbo.[Counter] SET
@LastValue = LAST_VALUE = LAST_VALUE + 1
, LAST_UPDATED = GETDATE()
WHERE COUNTER_NAME = @CounterName;
-- If doesn't exist (less likely case as only the first time the counter is accessed)
IF @@ROWCOUNT = 0 BEGIN
SET @LastValue = 1;
-- Create a new record if none exists
INSERT INTO dbo.[Counter] (COUNTER_NAME, LAST_VALUE, LAST_UPDATED)
SELECT @CounterName, @LastValue, GETDATE();
END;

这减少了访问并加快了SP的速度。

---

然而，再次假设您需要滚动自己的序列，如果您经常使用它来复制数据，您最好修改存储过程，一次性分配一个值块，即当您调用SP时，您传递一个参数@NumValuesRequired，然后您的返回值是那么长的块中的第一个。然后你可以一次完成你的副本，例如

DECLARE @RecordCount int, @CounterStart int;
SELECT @RecordCount = COUNT(*)
FROM Source_Table
WHERE ...;
EXEC uspGetCounter2 'Counter_123', @RecordCount, @CounterStart OUTPUT;
INSERT INTO Target_Table (Val1, Val2, Val3, CounterCol)
SELECT val1, val2, val3, @CounterStart + ROW_NUMBER() OVER () - 1
FROM Source_Table
WHERE ...;

Answer 2

首先，该过程位于错误的位置。INSERT语句的这一部分需要列名称，而不是列值。如果可以以这种方式运行(但不能(，那么您对该过程所做的任何操作都将进入该语句的SELECT子句。

顺便说一句，现有的Counter表和存储过程已经过时了。它们的存在是为了解决一个问题，这个问题后来被作为核心功能引入SQL Server。因此，这里的BEST解决方案是将计数器(所有计数器(转换为使用SEQUENCE和NEXT VALUE FOR语法。

然后，假设您已经将序列初始化为正确的值，则INSERT将如下所示：

INSERT INTO Target_Table (col1, col2, col3, [Counter_123])
SELECT (val1, val2, val3, NEXT VALUE FOR Counter_123) 
FROM Source_Table
WHERE ...

如果出于某种原因，这不是一个选项，我会将计数器逻辑滚动到查询中，以在单个事务中运行，而不是在许多事务中运行

BEGIN TRANSACTION
DECLARE @LastValue int;
SELECT @LastValue = LAST_VALUE
FROM Counter (holdlock)
WHERE COUNTER_NAME = 'Counter_123'
INSERT INTO Target_Table (col1, col2, col3, [Counter_123])
SELECT val1, val2, val3, @LastValue + ROW_NUMBER() OVER (ORDER BY val1, val2, val3) 
FROM Source_Table
WHERE ...
UPDATE Counter
SET LAST_VALUE = (SELECT MAX([Counter_123]) FROM [Target_Table])
,LAST_UPDATED=current_timestamp
WHERE COUNTER_NAME = 'Counter_123'
COMMIT