[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Having a table that is referenced in several tables, how can I execute a script that attempts to delete every record in that table without terminating on the first exception?

Posted on 2011-10-31
11
Medium Priority
?
280 Views
Last Modified: 2012-05-12
I have a table that is used in several other tables as a foreign key. If the table is referenced in only one specific table, I want the delete to be allowed and to cascade on delete. However, if there exists references in other tables, the delete should fail.

I want to test this with my data set by attempting to delete every record. No records should be deleted except for the last one. However, when I attempt to delete every record, it errors (as expected) and terminates the rest of the statement (doesn't proceed past the error).

How can I write a script that attempts to delete every record in a table and not terminate the statement on the first error?

Regards,

farminsure
0
Comment
Question by:farminsure
  • 6
  • 3
  • 2
11 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37059833
That's tricky.  If they are actual SQL Foreign Key references, SQL validates all that *before* you ever get control back, even in an AFTER trigger.

I think your only chance would be an INSTEAD OF DELETE trigger.  Hopefully SQL will allow that to get control before checking FK restrictions so you could do whatever checks you needed and only re-issue the DELETE on the row(s) you actually wanted to be deleted.
0
 
LVL 6

Expert Comment

by:judgeking
ID: 37064004
Are you looking for an actual SQL Trigger?  If so, what about a Stored Procedure instead?  Or are you just looking for a VBScript or Javascript file?
0
 
LVL 6

Expert Comment

by:judgeking
ID: 37079749
Any update?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:farminsure
ID: 37083452
This is the script I've settled on; however, it took 15.5 hours to iterate over 277,000 records. Any improvement would be welcome.

 
DECLARE @DeletedID Int

DECLARE @DeletedItems Table
(
	DeletedId int
);

DECLARE ItemsToDelete SCROLL CurSor For
	SELECT ID
	FROM TABLE

Open ItemsToDelete

SELECT COUNT(*)
  FROM TABLE

FETCH NEXT FROM ItemsToDelete INTO @DeletedID
	While @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		--ATTEMPT TO DELETE
		DELETE FROM TABLE WHERE ID = @DeletedID;
		Insert into @DeletedItems values (@DeletedID)
	END TRY	
	
	BEGIN CATCH
		--DO NOTHING
	END CATCH
	
	FETCH NEXT FROM ItemsToDelete INTO @DeletedID
END


SELECT COUNT(*)
  FROM TABLE

Select * From @DeletedItems

Close ItemsToDelete

Deallocate ItemsToDelete

Open in new window

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37083649
You would probably be much better off writing a query / code that could determine all the IDs that you want deleted, and only those rows, and then issue a single DELETE.

That is, use code to check all the (potentially) referenced tables and determine which ID(s) are eligible for deletion under your criteria, then delete just them.
0
 

Author Comment

by:farminsure
ID: 37083743
Hi Scott,

Do could you produce an example of determining the ID's prior to the delete statement? If this is possible, that would eliminate the fetching individual records and potentially cut script execution time very significantly.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37083784
Sure, give me some time.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1500 total points
ID: 37084142
Code below currently handles only a single FK column (that is, a REFERENCES with only one column, not more than one), but should give you an idea.  I can modify it *later* if needed to add more columns.  Or, based on it, you may be able to code out the rest by hand yourself for multi-column FKs.

USE ctsi_FSobj --specify db name containing table to delete rows from

SET NOCOUNT ON

IF OBJECT_ID('tempdb.dbo.#sql') IS NOT NULL
    DROP TABLE #sql

CREATE TABLE #sql (
    sqlId int IDENTITY(1, 1) NOT NULL,
    sql varchar(8000) NOT NULL
    )

DECLARE @schemaname varchar(128)
DECLARE @tablename varchar(128)
DECLARE @tablename_to_allow_rows_in_and_still_do_delete varchar(128)
DECLARE @sql varchar(8000)
DECLARE @sqlId int

SET @schemaname = 'dbo'
SET @tablename = 'table_to_delete_data_from'
SET @tablename_to_allow_rows_in_and_still_do_delete = 'ref_table_to_ignore'

INSERT INTO #sql SELECT
'DELETE FROM [' + @schemaname + '].[' + @tablename + ']'
INSERT INTO #sql SELECT
'FROM [' + @schemaname + '].[' + @tablename + '] AS tbl'
INSERT INTO #sql SELECT
'WHERE (1 = 1) AND'
INSERT INTO #sql
SELECT '( NOT EXISTS(SELECT TOP (1) NULL FROM ' + 
    '[' + CAST(refer_sch.name AS varchar(128)) + '].' +
    '[' + CAST(refer_o.name AS varchar(128)) + '] AS ref ' +
    'WHERE [ref].[' + refer_c.name + '] = [tbl].[' + parent_c.name + '] ) AND'
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects refer_o ON refer_o.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas refer_sch ON refer_sch.schema_id = refer_o.schema_id
INNER JOIN sys.columns refer_c ON refer_c.object_id = fkc.referenced_object_id AND refer_c.column_id = fkc.referenced_column_id
INNER JOIN sys.columns parent_c ON parent_c.object_id = fkc.parent_object_id AND parent_c.column_id = fkc.parent_column_id
WHERE (@tablename_to_allow_rows_in_and_still_do_delete IS NULL OR 
    refer_o.name <> @tablename_to_allow_rows_in_and_still_do_delete)

SET @sqlId = SCOPE_IDENTITY()

UPDATE #sql
SET sql = LEFT(sql, LEN(sql) - 4) --remove trailing " AND"
WHERE sqlId = @sqlId

SELECT sql AS [--DELETE command(s) to be run, NOT run yet.]
FROM #sql
ORDER BY sqlId

SET NOCOUNT OFF

Open in new window

0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1500 total points
ID: 37084333
CORRECTION!
Need to adjust the main SELECT WHERE clause as follows:

WHERE
    fkc.parent_object_id = (SELECT object_id FROM sys.objects o2 INNER JOIN sys.schemas s2 ON s2.schema_id = o2.schema_id
    WHERE o2.name = @tablename AND s2.name = @schemaname)
AND
    fkc.constraint_column_id = 1 --can add other columns by hand to WHERE clause of output if desired
AND
    (@tablename_to_allow_rows_in_and_still_do_delete IS NULL OR refer_o.name <> @tablename_to_allow_rows_in_and_still_do_delete)
0
 

Author Closing Comment

by:farminsure
ID: 37084519
This will definitely run faster than my generic script; however, I would need to weigh the benefit vs cost of having to maintain the script with accurate joins.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37084888
True.  Very best to extend the script to handle *all* columns in the FK, then just generate the full code every time you need it.

But, the code above *does* find *all* FK references for you, so all you have to do by hand is add all columns *past the first one*: luckily, often FKs are only one column.

And, as I'm sure you know, all you need to do for a multi-column FK is add to:
(NOT EXISTS .... WHERE ref.<col1> = parent.<col1>
the other colums:
AND ref.<col2> = parent.<col2> --AND ...
) AND ...

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question