Running Stored Procedure If Errors

• scking

  SSCrazy

  Points: 2598

  More actions

  September 18, 2003 at 2:54 pm

  #84440

  I have a table that is created from a manually created Excel file. The file contains information to load three separate tables in the database. When I try to load the data using a stored procedure I get an error because it selects two records with the same key since it has different descriptions of the part. My method is to concatenate to compound keys and use 'NOT IN' to determine if the key is already loaded prior to selecting. This DOES NOT exclude two parts with the same key since the part is not yet loaded. How do I cause the stored procedure to load the part number that is first selected and skip over the error when it tries to load it the second time. I know that a cursor would work but isn't it also possible to process the stored procedure with errors?

• jpipes

  SSChampion

  Points: 12230

  More actions

  September 18, 2003 at 3:58 pm

  #474952

  Your procedure will have to do a self-join from itself to a derived table of itself that is grouped by the primary key you are describing. In order to accomplish this, you will more than likely need to dump the tables to keyless temp tables that have IDENTITY keys, so that you can do an INNER JOIN on the IDENTITY when making the final insert.

  --

  For instance, the procedure would pull the three tables into 3 temp tables with a PRIMARY KEY defined on an IDENTITY field. Once populated, you would do some like this:

  
  
  -- Example temp table:
  
  CREATE TABLE #Temp1
  
  (
  
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
  
  , MyPKField1 CHAR(10) NOT NULL
  
  , MyPKField2 CHAR(10) NOT NULL
  
  -- , OtherFields...
  
  )
  
  -- SELECT FROM the Main Table, Joining to a subset of itself
  
  -- with only unique keys.
  
  SELECT 
  
    a.MyPKField1
  
  , a.MyPKField2
  
  --, a.OtherFields...
  
  FROM #Temp1 a
  
    INNER JOIN 
  
    (
  
    SELECT 
  
      MIN(b.ID) AS FirstID
  
    , b.MyPKField1
  
    , b.MyPKField2
  
    FROM #Temp1 b
  
    GROUP BY 
  
      b.MyPKField1
  
    , b.MyPKField2
  
    ) AS c
  
      ON a.ID = c.FirstID
  
  

  HTH,

  Jay