sql - 如何在 SQL Server 中创建数据集并处理每条记录？

Question

我有下表:

CREATE TABLE [dbo].[proposalReviewAction]
(
    [proposalReviewActionID] [INT] IDENTITY(1,1) NOT NULL,
    [proposalPackageID] [INT] NULL,
    [approvalTypeID] [INT] NULL,
    [comments] [VARCHAR](2000) NULL,
    [reviewedByID] [INT] NULL,
    [reviewedDate] [DATETIME] NULL
) ON [PRIMARY]

我想返回每个具有 approvalTypeID = 100 的记录，并在将数据从提案表移动到项目表的另一个过程中使用 proposalPackageID。

我考虑过使用 WHILE 循环，但似乎每个人都喜欢处理数据集，但我找不到在 SQL/T-SQL 中执行此操作的任何方法。

我找到了一个 WHILE 循环的例子并修改了它......

WHILE (SELECT[approvalTypeID] FROM [proposalReviewAction]) = 100
BEGIN  
    DECLARE @ppID AS INT ;

    SELECT [proposalPackageID] AS ppID, [approvalTypeID] AS atID 
    FROM [proposalReviewAction]

    IF (SELECT [approvalTypeID] AS atID FROM [proposalReviewAction]) = 100
        PRINT @ppID
    ELSE
        BREAK
END  

PRINT 'I got to the print statement'; 

...但我收到以下错误:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

然后，它打印“PRINT”语句。

感谢任何帮助...

鲍勃

Answer 1

您可以简单地将这些记录直接插入您的ProjectTable!喜欢:

INSERT INTO [ProjectTable] (...)
SELECT proposalPackageID 
FROM [proposalReviewAction]
WHERE approvalTypeID  = 100

如果您出于其他原因需要循环，您可以使用游标而不是WHILE。使用游标 可以帮助您逐条记录地获取变量查询的结果，并分别处理每条记录:

DECLARE @proposalPackageID INT

DECLARE product_cursor CURSOR FOR

    SELECT proposalPackageID 
    FROM dbo.proposalReviewAction 
    WHERE approvalTypeID = 100

OPEN product_cursor  
FETCH NEXT FROM product_cursor INTO @proposalPackageID
WHILE @@FETCH_STATUS = 0
BEGIN

  -- Insert directly into projectTable or call external process  
  INSERT INTO [projectTable] (proposalPackageID ...)
  SELECT @proposalPackageID


  FETCH NEXT FROM product_cursor INTO @proposalPackageID 
END  
CLOSE product_cursor  
DEALLOCATE product_cursor 

希望对你有帮助