SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 DELETE and gracefully PRINT error if issues
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 08/15/2013 :  10:50:01  Show Profile  Reply with Quote
I'm trying to delete a lot of customer related data. There are 100+ tables to delete the data from. What I want my delete queries to do is log if for some reason a constraint won't allow me to delete. I'm doing BULK deletes from a staging table, no row-based/cursor deletions as the number of customers to delete is too large for that and time constraints prevent row-based/cursor deletions. What is the best way to do this? Just a simple example query to help me on my way would be helpful.

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/15/2013 :  11:23:12  Show Profile  Reply with Quote
The ideal option, if possible, would be to understand the constraints and delete the data from the tables in the right order. That is not always easy, and perhaps is time-consuming.

Another option is to set up cascade deletes. That will cause the dependent rows in the dependent table to be deleted when a row from the table is deleted. That requires changing your database schema, and may not always be what you want.

A third option is to script all the constraints, delete or disable them, delete the rows and then re-enable or re-insert the constraints. This is not a safe operation on critical databases because if you end up not deleting some required data that can affect the data integrity.

Yes, I know - none of them are answers that you want to hear. So hang in there - some of the brilliant people on SQLTeam might have better suggestions.
Go to Top of Page

Brittney10
Posting Yak Master

USA
153 Posts

Posted - 08/15/2013 :  11:26:42  Show Profile  Reply with Quote
I currently have the delete statements set up to delete the data from the tables in the right order. Everything does delete without issues. However, "IF" I were to run into a constraint issue for whatever reason, I want to be able to catch this error. Thanks for the input. No luck on the web, so i'll see what i can figure out and post my solution if i find a good one. Thanks!!!!
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3742 Posts

Posted - 08/15/2013 :  11:33:18  Show Profile  Reply with Quote
You could use a transaction within a try catch. Here is a simplified example. If that works for you, you should follow the pattern outlined in example C on this page http://technet.microsoft.com/en-us/library/ms175976.aspx

CREATE TABLE XYZ(id INT NOT NULL PRIMARY KEY CLUSTERED);
CREATE TABLE ABC(id INT NOT NULL REFERENCES XYZ(id));

INSERT INTO XYZ VALUES (1),(2),(3);
INSERT INTO ABC VALUES (1);


BEGIN TRY
	BEGIN TRAN

		DELETE FROM XYZ WHERE id = 1;
	COMMIT TRAN
END TRY
BEGIN CATCH
	-- raise error here
	ROLLBACK
END CATCH;

DROP TABLE ABC;
DROP TABLE xyz;
To reiterate, I spent only 4 minutes and 11 seconds typing this - i.e., minimal testing. If you are going to use it on a critical system PLEASE test and satisfy yourself that that is what you want.
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/15/2013 :  11:36:01  Show Profile  Reply with Quote
quote:
I'm doing BULK deletes from a staging table, no row-based/cursor deletions as the number of customers to delete is too large for that and time constraints prevent row-based/cursor deletions. What is the best way to do this?
The best way may be to do it in batches. But hard to say as we don't know how may rows are being deleted, etc..

quote:
Everything does delete without issues. However, "IF" I were to run into a constraint issue for whatever reason, I want to be able to catch this error.
I'm not sure what you want to do with the eror, but after every DML statement you can check @@ERROR to see if an error was generated and stop processing at that point. Or, the more modern way is to use a TRY..CATCH block.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000