Inserting Rows
To add data to a table in the database, you would generally use the INSERT statement. The INSERT statement appends one or more rows of data to a table. The syntax for the INSERT statement follows, with Table 7.1 describing the main arguments of the 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
}
}
| DEFAULT VALUES
}
The values that are specified in the INSERT can either be actual values or data retrieved using a SELECT statement.
Inserting Rows with INSERT…VALUES
The first method we will look at for inserting data into a table is the INSERT statement using the column list and VALUES clause to specify the data to be inserted. Using the customer_demo table that we created in the previous section, let's add some data to the table and then display it by selecting it. Listing 7.1 contains the code to
Create the customer_demo table
Insert data into the table
Select the data
Drop the table when finished
Code Listing 7.1. Creating a Table and Inserting Rows
(1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) cust_id fname lname addr1 addr2 city zip ----------- ------------ ------------- ------- ------- ------ ------ 1 John Davolio NULL NULL NULL NULL 1 Anne Callahan NULL NULL NULL NULL 1 Steven Suyama NULL NULL NULL NULL 1 Robert Buchanan NULL NULL NULL NULL 1 Janet Peacock NULL NULL NULL NULL 1 Andrew Leverling NULL NULL NULL NULL (6 row(s) affected)
You can see that there were six individual messages from the server, one for each of the INSERT statements that were executed. In addition, because we did not specify any address information, these fields display NULL to signify that these columns are empty.
Tip
If you don't want the row count displays in the output, you can turn them off by using the NOCOUNT option as the first statement at the beginning of a procedure:
SET NOCOUNT ON
The data that you have inserted is returned to you in the order in which you inserted it. Because you did not specify an ORDER BY clause in the SELECT and there are no indexes on your table, the data is kept in the table, and returned to you, in the order in which it was inserted.
To insert rows, you specify the columns you want to insert within parentheses following the table name. You then follow that list with the keyword VALUES and specify the values for the row in the parentheses. If you do not specify a column list, the values you specify must be in the order in which they appeared in the original CREATE TABLE statement.
Tip
If you are using the INSERT statement in an application program, you should always provide a column list. This will prevent any problems if the table is dropped and re-created with the columns in a different order.
When specifying data to be inserted into a table, you must specify it based on the data type of each column. In the previous example, you could see that the first and last name values are specified using quotes. Table 7.2 lists the different data types and how to specify the data, including what to watch for.
Inserting Rows with INSERT…SELECT
In this method of inserting data, we will use the SELECT subquery instead of specifying the columns and respective values. The SELECT subquery in the INSERT statement can be used to add values into a table from one or more other tables or views. Using a SELECT subquery also enables you to insert more than one row at a time. The example in Listing 7.2 shows how to create the customer_demo table using the data from the employees table as the input.
Code Listing 7.2. Inserting Data Using a Subquery
Caution
The select list of the subquery must match the column list of the INSERT statement. If no column list is specified, the select list of the subquery must match the columns in the table or view being inserted into.
The resulting output of the statement in Listing 7.2 would be exactly the same as Listing 7.1, in which you specified the values to input. If you chose, you could have specified the address information from the employees table to insert into the customer_demo table.
Inserting Rows with SELECT INTO
There is one other method for inserting rows that you can use. The SELECT INTO statement provides a mixture of functionality. First, it creates a new table based on the structure of the existing table being specified in the statement. The following example provides the same functionality as the previous INSERT INTO statement. However, it does not need to define the columns in the new table, nor does it need to create the new table itself.
select employeeid,
firstname,
lastname,
Address,
city,
PostalCode
Into customer_Demo
from employees
All the employees in the employees table were copied to a new table, customer_demo.
You may select specific columns by using the SELECT INTO statement to create a table with fewer fields than the original table (as shown earlier), or you could create a complete copy of the original table as shown here:
select * Into customer_Demo from employees
Note
The SELECT INTO statement requires that the special option 'SELECT INTO.BULKCOPY' be turned on in the database properties. This option is set by the SQL Server administrator to provide the ability to load an entire table from another table.
Choosing which of these different methods to insert data with is entirely up to you and your particular need in a given application.
Tip
If you need to create large amounts of test data for an application, you can do so very quickly by using the INSERT…SELECT version of the INSERT statement. Using the following example, you could copy the data in a table back to itself, thus duplicating the information:
Insert customer_demo Select * from customer_demo