For this demonstration on Data Manipulation Language (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 column that will auto-number values for that column.

CREATE TABLE dbo.TestTable
(TestID int IDENTITY,
 TestCode char(2),
 TestName varchar(30),
 TestDate date);

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.

 INSERT dbo.TestTable
 VALUES ('R1', 'Row 1', '20080612'),
	    ('R2', 'Row 2', '20090713'),
		('R3', 'Row 3', '20100814'),
		('R4', 'Row 4', '20110915')

To see the results of our INSERT statement we run a simple SELECT statement.

SELECT * FROM dbo.TestTable

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. 

UPDATE dbo.TestTable
SET TestDate = GETDATE()
WHERE TestID = 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. 

DELETE dbo.TestTable
WHERE TestCode IN('R2', 'R4')

When we run the SELECT statement again, notice that the date for the record that had a TestID value of 3 changed to 2012-08-06. (That was the current date when this demonstration was originally made). Also, notice that the Rows that had a TestCode value of R2 and R4 have been removed.

SELECT * FROM dbo.TestTable