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 有时可能会为值 that will then fail on CONVERT 返回 1 。例如，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)

编辑
与其直接使用 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)，但是可以根据 NULL 值应该发生的情况进行调整RawdataTable。