Cursors Or While Loop what will be a good option to work on 6,00,000+ rows data

Question

I have a table_A where i imported a data from .csv file. I want to compare some of the columns from table_A with table_B and accordingly insert a data into a table and update a status to table_A. what should i use While loop or Cursor.. Plz do suggest

DECLARE @A_2 VARCHAR(10), @A_3 VARCHAR(50), @A_4 VARCHAR(100)
    DECLARE db_cursor CURSOR FOR  
    SELECT A_2, A_3, A_4 FROM Temp_table

OPEN db_cursor  
    FETCH NEXT FROM db_cursor   
    INTO @A_2, @A_3, @A_4 
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    If @A_2 <> (SELECT C_2 FROM Table_C where C_2= @A_2)
        UPDATE Temp_table SET [Status]='Not Exits in Table_C'
    ELSE BEGIN
       IF @A_3=(Select B_3 from Table_B where B_3=@A_3) AND @A_2=(SELECT B_2              FROM Table_B where B_2= @A_2)
       UPDATE Temp_table SET [Status]='Duplicate Row, Already Exists'
       ELSE 
            IF (@A_4 <>'B_4 '+'B_5')
            UPDATE Temp_table SET [Status]=' Format is not accepted '
            ELSE
            INSERT INTO Table_B(B_2, B_3, B_4) VALUES(@A_2, @A_3, @A_4)

    END
  CLOSE db_cursor  DEALLOCATE db_cursor    
  FETCH NEXT FROM db_cursor  INTO @A_2, @A_3, @A_4
    END

Here Temp_table is the table where i will be importing data from .csv file and comparison will be done using columns of Table_B, Table_C with Temp_Table will it be correct way to do?

The big mind-shift with relational databases is to get away from procedural thinking - while and curosrs - and get into thinking about sets. SQL is great at comparing huge amounts of data and acting on it. Embrace SQL! — n8wrl, Aug 25 '17 at 19:05
Bit open ended, and other options are available, depending on performance you could use a straight query (no need for a loop or cursor, set based approach would be better), or use SSIS as an option for more linear thinking if the data set gets too large / perf is an issue — Andrew, Aug 25 '17 at 19:05
If you wish to share some sample data, how you plan to compare them, and what you intend to insert into a new table as a result, we can help out writing that SQL. Most likely all three of those steps is 1 SQL statement. — JNevill, Aug 25 '17 at 19:09

score 0 · Answer 1 · answered Aug 25 '17 at 19:09

I am not too familiar with performance, but I would not recommend a while loop because it will be really slow. I would do something from like:

UPDATE table1
SET table1.column1 = table2.column1,
    table1.column2 = table2.column2
WHERE (do a select statement or column comparison)

This is just a general guideline and I hope it can help you get started. Otherwise you'll have to be a lot more specific.

References for updating and comparing:

How do I UPDATE from a SELECT in SQL Server?

Microsoft UPDATE

Shawn · Answer 2 · 2017-08-25T20:22:22.913

Neither. If you're working with SQL, the first step is to move away from thinking procedurally and move towards set-based thinking.

To do what you're looking to do, use a MERGE. https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql

SETUP

IF OBJECT_ID('tempdb..#table_A') IS NOT NULL
  DROP TABLE #table_A

CREATE TABLE #table_A (col1 int, col2 int, colX int, checkme bit DEFAULT 0) ;
INSERT INTO #table_A (col1, col2, colX)
VALUES 
      (1,1,1)
    , (2,2,2)
    , (3,3,3)
    , (4,4,4)
;

IF OBJECT_ID('tempdb..#table_B') IS NOT NULL
  DROP TABLE #table_B

CREATE TABLE #table_B (col1 int, col2 int, colX int) ;
INSERT INTO #table_B (col1, col2, colX)
VALUES 
      (0,0,1)
    , (0,0,2)
;

BEFORE

SELECT * FROM #table_A ;     
SELECT * FROM #table_B ;

QUERY TIME

MERGE #table_B AS target  
USING ( SELECT col1, col2, colX FROM #table_A ) AS source (col1, col2, colX)  
ON (target.colX = source.colX)  
WHEN MATCHED THEN   
    UPDATE SET col1 = source.col1
        , col2 = source.col2 
WHEN NOT MATCHED THEN  
    INSERT (col1, col2)  
    VALUES (source.col1, source.col2) 
;

AFTER

SELECT * FROM #table_A ;     
SELECT * FROM #table_B ;

I think you can also use the MERGE to update the flag in #table_A, but I don't know the correct syntax. If you need to, you can also just run another UPDATE after the MERGE.

MARK TABLE_A FLAG

UPDATE #table_A
SET flag = 1
FROM #table_B
WHERE #table_A.col1 = #table_B.col1
    AND #table_A.col2 = #table_B.col1
    AND #table_B.colX IS NOT NULL
;

AFTER AFTER SELECT * FROM #table_A ;

MORE MERGE GOODIES: http://www.made2mentor.com/2012/07/got-the-urge-to-merge/

score -1 · Answer 3 · answered Aug 25 '17 at 19:14

If you want to improve performance then you need to look at SET based operations. While loop and cursor is basically the same thing.

Refer the Below link for more details

http://www.techrepublic.com/blog/the-enterprise-cloud/comparing-cursor-vs-while-loop-performance-in-sql-server-2008/

Cursors Or While Loop what will be a good option to work on 6,00,000+ rows data

3 Answers3