SQL Server 游标

Question

我想将数据从一个表（原始数据，所有列都是 VARCHAR）复制到另一个表（使用相应的列格式进行格式化）。

为了将数据从 rawdata 表复制到 formatted 表中，我使用游标来识别受影响的行。我需要在错误日志表中记录该特定行，跳过它，然后继续复制剩余的行。

复制需要更多时间。还有其他方法可以实现这一目标吗？ 这是我的查询

DECLARE @EntityId Varchar(16) ,
        @PerfId Varchar(16), 
        @BaseId Varchar(16) ,
        @UpdateStatus Varchar(16) 

DECLARE CursorSample CURSOR FOR 
     SELECT EntityId, PerfId, BaseId, @UpdateStatus
       FROM RawdataTable 
     --Returns 204,000 rows

OPEN CursorSample 
FETCH NEXT FROM CursorSample INTO @EntityId,@PerfId,@BaseId,@UpdateStatus

  WHILE @@FETCH_STATUS = 0 
  BEGIN 
    BEGIN TRY
    --try insertting row in formatted table

    Insert into FormattedTable
      (EntityId,PerfId,BaseId,UpdateStatus)
    Values
      (Convert(int,@EntityId),
       Convert(int,@PerfId),
       Convert(int,@BaseId),
       Convert(int,@UpdateStatus)) 
    END TRY
  BEGIN CATCH
  --capture Error EntityId in errorlog table

   Insert into ERROR_LOG
     (TableError_Message,Error_Procedure,Error_Log_Time)
   Values 
     (Error_Message()+@EntityId,’xxx’, GETDATE())

END CATCH

FETCH NEXT FROM outerCursor INTO @EntityId, @BaseId 
END 

CLOSE CursorSample 
DEALLOCATE CursorSampler –cleanup CursorSample

Answer 1

您应该能够使用 INSERT INTO 语句将记录直接放入格式化表中。 INSERT INTO 的性能比使用游标好得多。

INSERT INTO FormattedTable
SELECT
    CONVERT(int, EntityId),
    CONVERT(int, PerfId),
    CONVERT(int, BaseId),
    CONVERT(int, UpdateStatus)
FROM RawdataTable
WHERE
    IsNumeric(EntityId) = 1
    AND IsNumeric(PerfId) = 1
    AND IsNumeric(BaseId) = 1
    AND IsNumeric(UpdateStatus) = 1

请注意，IsNumeric 有时可能会为 将在 CONVERT 时失败。例如，IsNumeric('$e0') 将返回 1，因此您可能需要创建一个更强大的用户定义函数来确定字符串是否为数字，具体取决于您的数据。

另外，如果您需要无法移动到格式化表中的所有记录的日志，只需修改 WHERE 子句：

INSERT INTO ErrorLog
SELECT
    EntityId,
    PerfId,
    BaseId,
    UpdateStatus
FROM RawdataTable
WHERE
    NOT (IsNumeric(EntityId) = 1
    AND IsNumeric(PerfId) = 1
    AND IsNumeric(BaseId) = 1
    AND IsNumeric(UpdateStatus) = 1)

EDIT
与其直接使用 IsNumeric，不如创建一个自定义 UDF 来告诉您字符串是否可以转换为 int。这个函数对我有用（尽管测试有限）：

CREATE FUNCTION IsInt(@value VARCHAR(50))
RETURNS bit
AS
BEGIN
    DECLARE @number AS INT
    DECLARE @numeric AS NUMERIC(18,2)
    SET @number = 0
    IF IsNumeric(@value) = 1
      BEGIN
        SET @numeric = CONVERT(NUMERIC(18,2), @value)
        IF @numeric BETWEEN -2147483648 AND 2147483647
            SET @number = CONVERT(INT, @numeric)
      END

    RETURN @number
END
GO

用于插入格式化表的更新的 SQL 看起来像这样：

INSERT INTO FormattedTable
SELECT
    CONVERT(int, CONVERT(NUMERIC(18,2), EntityId)),
    CONVERT(int, CONVERT(NUMERIC(18,2), PerfId)),
    CONVERT(int, CONVERT(NUMERIC(18,2), BaseId)),
    CONVERT(int, CONVERT(NUMERIC(18,2), UpdateStatus))
FROM RawdataTable
WHERE
    dbo.IsInt(EntityId) = 1
    AND dbo.IsInt(PerfId) = 1
    AND dbo.IsInt(BaseId) = 1
    AND dbo.IsInt(UpdateStatus) = 1

处理 NULL 可能有点奇怪（如果传入 NULL，我的函数将返回 0，即使尽管 INT 当然可以为 null），但是可以根据 RawdataTable 中的 NULL 值应该发生的情况进行调整。

Answer 2

您可以在游标定义中放置一个 WHERE 子句，以便首先只选择有效记录。您可能需要创建一个函数来确定有效性，但它应该比循环它们更快。

实际上，您可能想要创建一个包含无效记录的临时表，以便可以记录错误，然后仅在临时表中没有的行上定义游标。

Answer 3

插入比光标工作得更好。
由于光标仅在您的 PC 内存中工作，并且会减慢 SQL Server 的优化速度。我们应该避免使用游标，但（当然）有些情况下无法避免使用游标。