DELETE and gracefully PRINT error if issues

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DELETE and gracefully PRINT error if issues

Author

Topic

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-08-15 : 10:50:01

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 11:23:12

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.

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-08-15 : 11:26:42

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-15 : 11:33:18

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-15 : 11:36:01

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources