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

 

 

Answer 1

Have you consider using MERGE

http://technet.microsoft.com/en-us/library/bb510625.aspx

---

With kind regards
Krystian Zieja
http://www.projectnenvision.com
Follow me on twitter
My Blog

Answer 2

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

Answer 3

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..

Answer 4

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

Answer 5

My database is SQL server 2000 but MERGE is supported by SQL server 2008 it seems!!!...Any other options to do it logical ways?...

Answer 6

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, [e-mail]
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

Answer 7

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.

Answer 8

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, [e-mail]
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