I have a combination of 2 tables (Header/Detail) that will have Millions of rows within a year or so. I have to do an Insert into the detail table if the Header information exists and an Insert into Both if it does not exist. there
are several ways to do this (if Exists, If not exists, attempt the insert and catch a restraint violation).
I was hoping someone could recommend the most efficient way to do this as opposed to me setting up several variations and try to "Time it" and see which is bettter.
the header contains an identity column, Claim Number varchar(38), Partner varchar(30) and 3 other small varchar fields.
the detail column has an identity column, a Bigint column that matches to the Identity from the Header, a "Date" field that is char(08) "CCYYMMDD" format, batchNumber varchar(10) and 2 other varchar fields.
it the Claim Number / Partner already exists on the Header table, I want to insert a new Detail record (Using the Old HeaderKey) and if it does not exist, I will insert the Header and then insert the Detail record (using the New headerKey).
any suggestions are appreciated.
View Complete Post