SQLDenis said:
yeah I once forgot to comment it out after I ran the select, can you guess what happened?
Code:
delete test1
select *
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield
1. Never,
never,
NEVER put the comment in front of the select. Put it in front of the data modification statement.
2.
Use an alias in the delete or update clause, NOT the table name. This will help protect you no matter what you end up running, even if you run just the delete line by itself (unless you happen to have a table named by the alias you use, so don't use that table as an alias or don't name tables that short).
Code:
select *
[red][b]--[/b][/red] delete [s]test1[/s] [b][red]t[/red][/b]
from test1 t join
(select top 1000 idfield from test1 order by idfield) a
on t.idfield = a.idfield
To run the delete/update once you're satisfied with the select results, highlight starting just after the begin comment, through the end of the query, and hit F5. Don't remove the comment at any time.
And an unrelated #3 that comes to mind:
If you have a big .sql file you're working with that should NOT be run in its entirety, here's another runaway code protection. First,
CREATE DATABASE trash
Then, at the top of your SQL file:
USE DATABASE trash
BEGIN TRAN
GO
This way, if you hit F5 accidentally, you won't have the same kind of problems, unless somewhere in your script you have USE DATABASE commands and COMMIT TRANs--a practice I avoid, at least the use database command in dev scripts...
Of course, this also won't protect you if you use object names, but that's a reason to avoid object names in your dev/maintenance scripts. Use object names in your production code.
This little technique has saved me a headache several times.
a simple ROLLBACK TRAN and switch back to the database I want and I'm not left trying to correlate 100 errors + random statements that did *something* to the lines of script to see if I did anything seriously bad.
[COLOR=#aa88aa black]
Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]