NOTE:
For demos in this article, we will use a table named Customer that contains the following columns:
| Name | Data Type |
| FirstName | nvarchar(50) |
| LastName | nvarchar(50) |
| StreetAddress | nvarchar(255) |
| City | nvarchar(255) |
| State | char(2) |
| ZipCode | nvarchar(10) |
| TotalSales | decimal(18,2) |
In addition, I created a primary key on the CustID column and set it to autoincrement by setting the following properties:
| Is Identity | Yes |
| Identity Seed | 1 |
| Identity Seed | 1 |
Afer adding a couple rows to the table, the data looks like this.
| CustID | FirstName | LastName | StreetAddress | City | State | ZipCode | TotalSales |
| 1 | Steve | Smith | 900 Belle St | Detroit | MI | 48888 | 5000.00 |
| 2 | Ryan | Miller | 1 Shutout Ct | Buffalo | NY | 32323 | 250.00 |
We can use the INSERT command to add a new row to this table. The syntax of the INSERT command is
INSERT INTO [TableName]
(
[Column List]
)
VALUES
(
[Values List]
)
We can insert a row for a new Customer - Brad Van Pelt - with the following code.
INSERT INTO Customer
(
FirstName,
LastName,
StreetAddress,
City,
State,
ZipCode,
TotalSales
)
VALUES
(
'Brad',
'Van Pelt',
'99 Linebaker Ln',
'Owosso',
'MI',
'47777',
4000
)
Notice that each column name in the first set of parentheses matches a value in the second set of parentheses: 'Brad' with FirstName, 'Van Pelt' with LastName and so on. Notice also that we did not provide a value for the CustID column. This is because CustID is an identity column and, therefore, gets populated with an incremented number when a new row is added.
After executing the above INSERT statement, our data should look like this:
| CustID | FirstName | LastName | StreetAddress | City | State | ZipCode | TotalSales |
| 1 | Steve | Smith | 900 Belle St | Detroit | MI | 48888 | 5000.00 |
| 2 | Ryan | Miller | 1 Shutout Ct | Buffalo | NY | 32323 | 250.00 |
| 3 | Brad | Van Pelt | 99 Linebaker Ln | Owosso | MI | 47777 | 4000.00 |
In this article, we showed how to use T-SQL's INSERT, UPDATE, and DELETE commands to modify the data in a table.
The new customer was automatically assigned a CustID value of 3. Because this value uniquely identifies the newly-added row, we can use it to find and update that row. The syntax to update a row in SQL Server is
UPDATE [Table]
SET [Column1] = [New Value 1],
[Column2] = [New Value 2],
[Column3] = [New Value 3],
etc...
WHERE [Filter Condition]
Only rows that match the filter condition will be updated and only those columns specified in the SET clause will be updated. We will use the following command to update the StreetAddress, City, State and ZipCdoe columns of Customer 3:
UPDATE Customer
SET StreetAddress = '100 Safety St',
City='New York',
State='NY'
ZipCode='01111'
WHERE CustID = 3
After executing the above UPDATE command, our data should like this:
| CustID | FirstName | LastName | StreetAddress | City | State | ZipCode | TotalSales |
| 1 | Steve | Smith | 900 Belle St | Detroit | MI | 48888 | 5000.00 |
| 2 | Ryan | Miller | 1 Shutout Ct | Buffalo | NY | 32323 | 250.00 |
| 3 | Brad | Van Pelt | 100 Safety St | New York | NY | 01111 | 4000.00 |
We use the DELETE command to delete rows in a SQL Server table. The DELETE syntax is
DELETE Customer
WHERE CustID = 3
The following code will delete Customer 3
DELETE Customer
WHERE CustID = 3
After executing the above DELETE command, our data will look like this:
| CustID | FirstName | LastName | StreetAddress | City | State | ZipCode | TotalSales |
| 1 | Steve | Smith | 900 Belle St | Detroit | MI | 48888 | 5000.00 |
| 2 | Ryan | Miller | 1 Shutout Ct | Buffalo | NY | 32323 | 250.00 |
In this article, we showed how to use the INSERT, UPDATE and DELETE commands to modify data in a SQL Server table.