Inserting New Data

only for RuBoard

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

Figure 4.2. Results of the query to see if new products have been added.

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!

only for RuBoard