For this demonstration on Data Manipulation Languare (DML) statements we will create a table named dbo.TestTable with four columns. Then we will be using the INSERT statement to add records to the table, the UPDATE statement to modify existing records, and finally the DELETE statement to remove records from the table
While it is not required , it is a good idea to always use a WHERE statement with the DELETE and UPDATE. That way ALL the records in your table are not accidently deleted or updated. Many developers like to write them as a SELECT statement first to make sure they are working with the correct data.
So let’s start by creating our TestTable. This table will have four columnes named TestID, TestCode, TestName, and TestDate. Be sure to notice that TestID is an IDENTITY field that will auto-number values for that column.
After we create the dbo.TestTable let’s INSERT four records. There are many ways you can INSERT records into a table which you can read about here.
To see the results of our INSERT statement we run a simple SELECT statement.
Next, we will use the UPDATE statement to change the date of the TestDate field to the current date. Notice that we included a WHERE statement so only the TestDate field changed for the record that had a TestID of 3. Finally, we will use the DELETE statement to remove two records. Again, we will use the WHERE statement so that we only remove two records instead of all the records in the table. When we run the SELECT statement again, notice that the date for the record that had a TestID value of 3 changed to the current date (2012-08-06 when this demonstration was originally made). Also, notice that the Rows that had a TestCode value of R2 and R4 have been removed.