none
Need an Idea for doing bulk compare and insert

    Question

  • Hi Friends,

    I got a scenario where we want to move some 30k records from a staging table to main table while moving we need to compare the columns from staging to history table and insert some new columns along with the columns in staging tables.

     

    I will give one example.

    Lets take staging table with scol1, scol2, scol3

    Main table have mcol1, mcol2, mcol3, mcol4,mcol5,mcol6

    History table have hcol1,hcol2, hcol3,hcol4, hcol5,hcol6

    so i wil move the staging table to Main table by comparing columns scol1, scol2, scol3 with hcol1,hcol2, hcol3 and mcol4,mcol5,mcol6 will contain the 1 if values differ or 0 if it contains same value

     

     

    Friday, September 24, 2010 2:19 PM

Answers

  • Your question is not very clear. It always helps if you can include:

    1) CREATE TABLE statements for your tables (preferrably simplified for the problem at hand).
    2) INSERT statements with sample data.
    3) The desired result given the sample.
    4) Which version of SQL Server you are using. (You have now said that it is SQL 2000, but it was missing from your original post.)

    My interpretation of what you have said leads to something like:

    INSERT main (keycol, mcol1, mcol2, mcol3, mcol4, mcol5, mcol6)
       SELECT keycol, scol1, scol2, scol3,
              CASE WHEN scol1 = hcol1 THEN 1 ELSE 0 END,
              CASE WHEN scol2 = hcol2 THEN 1 ELSE 0 END,
              CASE WHEN scol3 = hcol3 THEN 1 ELSE 0 END
       FROM   staging s
       JOIN   history h ON h.keycol = s.keycol


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Kalman TothModerator Saturday, September 25, 2010 12:31 PM
    • Marked as answer by KJian_ Friday, October 01, 2010 6:27 AM
    Saturday, September 25, 2010 11:17 AM

All replies

  • Friday, September 24, 2010 2:25 PM
  • Assuming you have key comparison columns that both the history and staging table share you can do something like:

    INSERT  dbo.MainTable 
    SELECT  CASE WHEN H.hcol1 = S.scol1 THEN 1 ELSE 0 END 
        ,   CASE WHEN H.hcol2 = S.scol2 THEN 1 ELSE 0 END 
     
    FROM    History h 
        INNER JOIN 
            Staging s 
        ON  h.KeyCol = s.KeyCol 

     

    Hope this helps

    • Edited by SqlRockss Friday, September 24, 2010 2:34 PM Grammer mistake
    Friday, September 24, 2010 2:34 PM
  • now only i take a look at it...it sinvolves only two tables target(History) and source(Staging)..is that possible to use third table for insert, update and delete?. accoring to the conditions matched..
    Friday, September 24, 2010 2:51 PM
  • Yes MERGE involves only two tables if you cannot change your process it rather won't help.

     


    With kind regards
    Krystian Zieja
    http://www.projectnenvision.com
    Follow me on twitter
    My Blog
    Saturday, September 25, 2010 12:50 AM
  • My database is SQL server 2000 but MERGE is supported by SQL server 2008 it seems!!!...Any other options to do it logical ways?...
    Saturday, September 25, 2010 7:52 AM
  • Your question is not very clear. It always helps if you can include:

    1) CREATE TABLE statements for your tables (preferrably simplified for the problem at hand).
    2) INSERT statements with sample data.
    3) The desired result given the sample.
    4) Which version of SQL Server you are using. (You have now said that it is SQL 2000, but it was missing from your original post.)

    My interpretation of what you have said leads to something like:

    INSERT main (keycol, mcol1, mcol2, mcol3, mcol4, mcol5, mcol6)
       SELECT keycol, scol1, scol2, scol3,
              CASE WHEN scol1 = hcol1 THEN 1 ELSE 0 END,
              CASE WHEN scol2 = hcol2 THEN 1 ELSE 0 END,
              CASE WHEN scol3 = hcol3 THEN 1 ELSE 0 END
       FROM   staging s
       JOIN   history h ON h.keycol = s.keycol


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    • Proposed as answer by Kalman TothModerator Saturday, September 25, 2010 12:31 PM
    • Marked as answer by KJian_ Friday, October 01, 2010 6:27 AM
    Saturday, September 25, 2010 11:17 AM
  • Thanks Erland....sorry this is my first query in the forum..so this is the only way using  CASE?..it wont affect performance even im dealing with 30,000 records?...

    Thanks in Advance friends!!.. Rajkumar.
    • Edited by rajmahendran Saturday, September 25, 2010 12:22 PM grammer mistake
    Saturday, September 25, 2010 12:21 PM
  • From the problem description you have given, CASE appears to be the logical choice. But I need to add the disclaimer that with the very incomplete description you have, I might have misunderstood what you are asking for.

    The performance cost for the CASE expression is about negligible. The performance cost for database operations is more related to locating rows, and particularly if this requires reading data from disk. Thus, the fact that you need to read data from the history table at all may impact performance, particularly if there are no suitable indexes.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Saturday, September 25, 2010 2:45 PM