CODEWITHSUNDEEP
sqlsql-server
user1194919
user1194919

Reputation:

Error handling in SQL Server stored procedure

I have a T-SQL query in a stored procedure which inserts data into a table using SELECT command from another table. Problem is it stops query execution if it found duplicate entry in select table clause while inserting it in another table (I have imposed primarky key constraint on this)

I want SQL to skip the error occurred (i.e. don't throw it nd stop.. continue your execution) and go with next line of row inserting..

I know there are ways with TRANSACTION COMMIT ROLLBACK TRY CATCH but how to use them ?

My T-SQL :

Begin

    Set @SQL='Insert Into AxisReports
                Select *
                From ReportData L 
                Left Join ATM A On L.ATMID=A.ATM 
                Where L.ATMID=A.ATM AND L.IssuerNetwork < > ''0000'' '
    Exec(@SQL)

End

The source table may contain more than 5 Lac entries with very small no. of duplicate rows.

Upvotes: 0

Views: 478

Answers (2)

Saurabh R S
Saurabh R S

Reputation: 3177

Instead of interfering with the Keys of the tables I would suggest you to redesign your code so that duplicate rows shouldn't be allowed to go into your destination table at the first place.
You may change your INSERT SQL to something like this:

INSERT INTO AxisReports
SELECT x.ATMID FROM
    (SELECT * 
       FROM ReportData L 
  LEFT JOIN ATM A ON L.ATMID=A.ATM 
      WHERE L.ATMID=A.ATM) x
LEFT OUTER JOIN AxisReports y ON x.ATMID =  y.RepID
WHERE y.RepId IS NULL

Hope it helps.

Upvotes: 1

Jeff S
Jeff S

Reputation: 7484

Try using a Distinct clause in your query:

Begin

    Set @SQL='Insert Into AxisReports
                Select distinct *
                From ReportData L 
                Left Join ATM A On L.ATMID=A.ATM 
                Where L.ATMID=A.ATM AND L.IssuerNetwork < > ''0000'' '
    Exec(@SQL)

End

Note that this will only work if the values for all of the columns returned from the query are the same for the duplicate rows.

Upvotes: 0

Related Questions