To add data to a table in a SQL Server Express database, you--logically--use the INSERT statement. (For details, see "Using the INSERT Statement," InstantDoc ID 98366.) Just as logically, to remove rows from a table you can use the DELETE statement.
One important point to remember when you're using the DELETE statement is that it's very powerful. If you aren't careful, you can easily wind up deleting more rows than you intended. Using the WHERE clause to specify the rows to delete is more than just a best practice--with the DELETE statement, it’s almost always required. If you don’t include a WHERE clause, the DELETE statement deletes all the rows in the table. For example, the statement
- DELETE MyTable
will delete all the rows in the table named MyTable, although it won't delete the table itself.
If you delete all the rows in a table by accident and the DELETE statement is part of a transaction, you can recover the deleted rows by executing a ROLLBACK command. If the DELETE statement isn't part of a transaction, your only recourse is to restore the rows from a backup.
The WHERE clause specifies the rows that will be deleted. The statement
- DELETE MYTable WHERE ColumnID = 1
deletes the rows for which the value of the ColumnID column is equal to 1 from the table named MyTable.