Inserting New Data

The INSERT statement is used to create new rows of data in a database. It appends a new row to a table in the database. Listing 4.5 shows the basic structure of the INSERT statement.

Listing 4.5. The Basic Structure of the INSERT Statement

INSERT [ INTO]
    { table_name WITH ( < table_hint_limited > [ ...n ] )
        | view_name
        | rowset_function_limited
    }

    {    [ ( column_list ) ]
        { VALUES
            ( { DEFAULT | NULL | expression }  [ ,...n] )
            | derived_table
            | execute_statement
        }
    }

The following code example demonstrates how to add a new product to the Products table for Exotic Liquids using the SQL INSERT statement:

1: INSERT INTO Products
2: (
3: ProductName,
4: SupplierID,
5: CategoryID,
6: QuantityPerUnit,
7: UnitPrice,
8: UnitsInStock,
9: UnitsOnOrder,
10: ReorderLevel
11: )
12: VALUES
13: (
14: 'Fast Acting Pheromone Liquid', 
15: 1,
16: 1,
17: '1000 Per Case',
18: 100,
19: 100,
20: 100,
21: 10
22: )

The reason the ProductID value wasn't set is that it's a Primary Key and is Auto Incremented by SQL Server. This is done so we are guaranteed not to have duplicates. You didn't set the value for Discontinued because it has a default value of False. To make sure the new row was added to the Products table, you can run the following SELECT statement:

1: SELECT Products.*
2: FROM Products
3: WHERE SupplierID = 1

The results from the proceeding SQL SELECT query can be seen in Figure 4.2

Sure enough, Exotic Liquids' new Fast Acting Pheromone Liquid is now added to the table and ready for distribution. Looks like they're expecting it to be a top-seller!