sql server cursor

I want to copy data from one table (rawdata, all columns are VARCHAR) to another table (formatted with corresponding column format).

For copying data from the rawdata table into formatted table, I'm using cursor in order to identify which row is affected. I need to log that particular row in an error log table, skip it, and continue copying remaining rows.

It takes more time to copying. Is there any other way to achieve this? this is my query

DECLARE @EntityId Varchar(16) ,
        @PerfId Varchar(16), 
        @BaseId Varchar(16) ,
        @UpdateStatus Var开发者_如何学运维char(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

You should just be able to use a INSERT INTO statement to put the records directly into the formatted table. INSERT INTO will perform much better than using a cursor.

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

Note that IsNumeric can sometimes return 1 for values that will then fail on CONVERT. For example, IsNumeric('$e0') will return 1, so you may need to create a more robust user defined function for determining if a string is a number, depending on your data.

Also, if you need a log of all records that could not be moved into the formatted table, just modify the WHERE clause:

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
Rather than using IsNumeric directly, it may be better to create a custom UDF that will tell you if a string can be converted to an int. This function worked for me (albeit with limited testing):

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

The updated SQL for the insert into the formatted table would then look like this:

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

There may be a little weirdness around handling NULLs (my function will return 0 if NULL is passed in, even though an INT can certainly be null), but that can be adjusted depending on what is supposed to happen with NULL values in the RawdataTable.

You can put a WHERE clause in your cursor definition so that only valid records are selected in the first place. You might need to create a function to determine validity, but it should be faster than looping over them.

Actually, you might want to create a temp table of the invalid records, so that you can log the errors, then define the cursor only on the rows that are not in the temp table.

Insert into will work much more better than Cursor. As Cursor work solely in Memory of your PC and slows down the optimization of SQL Server. We should avoid using Cursors but (of course) there are situations where usage of Cursor cannot be avoided.