| Author |
Topic |
|
QueenKirsty
Starting Member
18 Posts |
 Posted - 2009-05-01 : 07:24:17
|
| I want to delete the data automatically from a set of tables but the data in some of the tables have dependent relationships with data in other tables. I would like to create an ordered list of tables that gives each table a number to represent what order data should be deleted. I know that the sysdepends table will give me the list of tables that have dependencies on other tables but does anyone have any suggestions on how I use this to build up a temporary table or view giving an "order for deletion" column? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 07:38:47
|
| This technique should work for you:http://www.sqlteam.com/article/performing-a-cascade-delete-in-sql-server-7 |
 |
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-05-01 : 08:33:24
|
Thanks. That comes from a different angle but looks like it will work well. I will give it a try. |
|
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-05-01 : 09:21:01
|
| Small problem - How can this be modified if I do not want to supply a where clause for the original table having its data deleted (i.e. I want to delete ALL the records from the table)? |
|
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-05-01 : 09:29:51
|
| OK - solved that one, put in WHERE 1+1=2 and it seems to take it.New problem - "Maximum stored procedure, function, trigger, or view nesting level exceeded(limit 32)." error message. What does this mean?Does this mean that the complexity of the tables is too great for the SP to handle? How can I fix this? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 10:33:56
|
| You could also use "WHERE 1=1", or modify the procedure and remove the WHERE condition entirely.If you're going to delete all the data in these tables, it's probably better to drop the foreign keys, truncate the tables, then recreate the FK's.The error you're getting is due to recursive calls, you'll have to figure out which tables are at the lowest levels and start with them. Change the procedure to only print out the delete statement, not execute it. Copy these to a query window and run them in order, a few statements at a time. |
|
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-05-01 : 11:01:23
|
| Sorry, I'm not sure I really understand. Are you saying that I would have to do it manually?This is not really feasible as I am going to have to rerun this on a regular basis so I need something automated. Any other suggestions?Also,"If you're going to delete all the data in these tables, it's probably better to drop the foreign keys, truncate the tables, then recreate the FK's."what does this mean? What will it achieve?(sorry if this seems like a stupid question. I am relatively new to this level of complexity in SQL) |
|
|
|
QueenKirsty
Starting Member
18 Posts |
Posted - 2009-05-01 : 11:59:58
|
| I have found the problem. The tables that didn't work were ones that were referencing themselves. All the others are getting deleted appriopriately!Is there a nice neat bit of code that will check if the table references itsself before running the SP?Many thanks for all assistance. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-05-01 : 12:31:07
|
| Not handy, but it should be easy to modify that code to ignore tables that reference themselves. |
|
|
|
|
Hierarchical list of table dependencies