# Friday, 27 March 2009
Back To Basics

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.