3.3. Inserting Values into a Table
There are several ways to insert values into a table using SQL in SQL Server . We
will illustrate the two most commonly used ways: using INSERT INTO .. VALUES and using
INSERT INTO .. SELECT.
3.3.1. Using INSERT INTO .. VALUES
One way to insert values into one row of a table is
to use the INSERT INTO command with the VALUES option. The INSERT INTO ..
VALUES option needs the column list and all the columns in the correct order.
The general syntax for the INSERT INTO .. VALUES option is:
INSERT INTO TableName
VALUES ('character_attribute_value', numeric_attribute_value, ...)
We will first illustrate inserting data with the INSERT INTO .. VALUES
option using the Names table we created in the preceding section. So, type the following in
the query editor:
INSERT INTO Names
VALUES ('Joe Smith')
where:
-
INSERT is the SQL command to insert data
-
INTO is a necessary keyword
-
Names is the name of an existing table
-
VALUES is another necessary keyword
-
'Joe Smith' is a string of letters corresponding to the
VARCHAR data type
Then click the Execute button. You will get a message that will tell you how many
rows were inserted by the query:
(1 row(s) affected)
Now, if you type the following SQL query:
SELECT *
FROM Names
You will get:
fullname
--------------------
Joe Smith
(1 row(s) affected)
The INSERT INTO .. VALUES option appends rows to a table (that is, rows are
added to the end of the table). So, if you use the INSERT INTO .. VALUES option again as
follows:
INSERT INTO Names
VALUES ('Sudip Kumar')
And then type:
SELECT *
FROM Names
You get this result:
fullname
--------------------
Joe Smith
Sudip Kumar
(2 row(s) affected)
If you created a table with n attributes (columns), you usually would
have n values in the INSERT INTO .. VALUES statement, in the order of the
definition of the columns in the table. For example, to insert into the Employee table that
you created earlier, the INSERT INTO .. VALUES statement to insert a row would have to
match column for column and would look like this:
INSERT INTO Employee
VALUES ('Joe Smith', '123 4th St.', 101, 2500)
Note that character data is entered with single quotes around it. Numeric data does
not use quotes (as shown by 101 and 2500).
Now if you type:
SELECT *
FROM Employee
You get the following:
names address employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith 123 4th St. 101 2500.00
(1 row(s) affected)
An INSERT that looks like the following is incorrect, because it does not
include all four columns of the Employee table:
INSERT INTO Employee
VALUES ('Joe Smith', '123 4th St.')
You may INSERT a row with less than all the columns by naming the columns
you want to insert into, like this:
INSERT INTO Employee (names, address)
VALUES ('Joe Smith', '123 4th St.')
In this case, the row will contain nulls or default values for the values left out,
which you will see if you type:
SELECT *
FROM Employee
This will give:
names address employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith 123 4th St. 101 2500.00
Joe Smith 123 4th St. NULL NULL
(2 row(s) affected)
An INSERT that looks like the following is incorrect, because it does not
have the values in the same order as the definition of the table:
INSERT INTO Employee
VALUES (2500, 'Joe Smith', 101, '123 4th St.')
If for some reason the data had to be entered in this order, the previous statement
could be corrected by specifying the column names, as shown here:
INSERT INTO Employee (salary, names, employee_number, address)
VALUES (2500, 'Joe Smith', 101, '123 4th St.')
At this point, typing:
SELECT *
FROM Employee
would give us the following output:
names address employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith 123 4th St. 101 2500.00
Joe Smith 123 4th St. NULL NULL
Joe Smith 123 4th St. 101 2500.00
(3 row(s) affected)
You may actually include the keyword, null, if the address and the
salary were unknown:
INSERT INTO Employee
VALUES ('Joe Smith', null, 101, null)
Now having added four rows to our table, type:
SELECT *
FROM Employee
This query will give the following output:
names address employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith 123 4th St. 101 2500.00
Joe Smith 123 4th St. NULL NULL
Joe Smith 123 4th St. 101 2500.00
Joe Smith NULL 101 NULL
(4 row(s) affected)
To delete all the rows in the Employee table as well as in the
Names table, type:
DELETE FROM Employee
Then:
DELETE FROM Names
We will revisit the DELETE command later in the chapter.
For the rest of this chapter, we will set up our Employee table with more
meaningful data. Suppose we deleted all the test rows from the previous examples with a
DELETE statement and then suppose we used the INSERT INTO .. VALUES option to
insert valid data into the Employee table, making it look like this:
names address employee_number salary
-------------------- -------------------- --------------- ------------
Joe Smith 123 4th St. 101 2500.00
Pradeep Saha 27 Shillingford 103 3300.00
Sumit Kumar 95 Oxford Rd 105 1200.00
Joya Das 23 Pesterfield Cr 114 2290.00
Terry Livingstone 465 Easter Ave 95 3309.00
(5 row(s) affected)
 |
More than one INSERT INTO .. VALUES command can be
typed in on one screen in SQL Server .
|
|
3.3.2. Using INSERT INTO .. SELECT
With the INSERT INTO .. VALUES option, you insert only one row at a time
into a table. With the INSERT INTO .. SELECT option, you may (and usually do) insert many rows into a table at one time.
The general syntax for the INSERT INTO .. SELECT option is:
INSERT INTO target_table(column1, column2, column3, ...)
"SELECT clause"
We will first illustrate inserting with the INSERT INTO .. SELECT by
populating the Names table (the one that you created earlier in this chapter and then
removed all rows from with a DELETE FROM Names). To copy all the names from the
Employee table into the Names table, type the following:
INSERT INTO Names(fullname)
SELECT names
FROM Employee
And now if you type:
SELECT *
FROM Names
you will get the following five rows of output:
fullname
--------------------
Joe Smith
Pradeep Saha
Sumit Kumar
Joya Das
Terry Livingstone
(5 row(s) affected)
We do not have to copy all the names from the Employee table to the
Names table. For example, we could restrict the INSERT .. SELECT like this:
INSERT INTO Names(fullname)
SELECT names
FROM Employee
WHERE salary > 2600
This would give us only the following two rows in Names:
fullname
--------------------
Pradeep Saha
Terry Livingstone
(2 row(s) affected)
As with the INSERT INTO .. VALUES option, if you create a table with
n columns, you usually would have n values in the INSERT INTO ..
SELECT option in the order of the table definition, or you would have to name the columns
you are inserting. For example, suppose we have a table called Emp1, created with three
columns:
Emp1 (addr, sal, empno)
The columns, addr, sal, empno, stand for address, salary,
and employee number, respectively.
Now suppose that we want to load the existing empty table called Emp1 from
the Employee table with the appropriate columns.
|
As with the INSERT INTO .. VALUES option, the
INSERT INTO .. SELECT option has to match column for column.
|
|
An INSERT INTO .. SELECT statement would look like this:
INSERT INTO Emp1(addr, sal, empno)
SELECT address, salary, employee_number
FROM Employee
The Emp1 table would now have the following five rows:
addr sal empno
-------------------- ------------ -----------
123 4th St. 2500.00 101
27 Shillingford 3300.00 103
95 Oxford Rd 1200.00 105
23 Pesterfield Cr 2290.00 114
465 Easter Ave 3309.00 95
(5 row(s) affected)
If we created a table, Emp2, with identical columns (or attributes) as
Emp1, we could use the following INSERT to load data from table Emp1 to
Emp2:
INSERT INTO Emp2
SELECT *
FROM Emp1
The Emp2 table would now have the same data as the Emp1 table. This
is one way of creating a backup table.
Again, note that the Emp2 table has to exist (be created with the same
columns and types) before loading it with the INSERT INTO .. SELECT option.
One caution must be pointed out, however. An erroneous INSERT INTO .. SELECT
could succeed if the data types of the SELECT match the data types of the columns in the
table to which we are inserting. For example, say we execute the following statement (remember that
both sal and empno are numeric types):
INSERT INTO Emp1 (addr, sal, empno)
SELECT address, employee_number, salary
FROM Employee
This INSERT will succeed because the data types match. The following output
results after executing the previous INSERT statement:
addr sal empno
-------------------- ------------ -----------
123 4th St. 101.00 2500
27 Shillingford 103.00 3300
95 Oxford Rd 105.00 1200
23 Pesterfield Cr 114.00 2290
465 Easter Ave 95.00 3309
(5 row(s) affected)
The wrong information has been inserted in Emp1's columns. The
employee_number from Employee has been inserted into the sal column in
Emp1, and the salary of Employee has been inserted into the
empno column of Emp1. So, be careful and line up or match up the columns
(attributes) in the INSERT INTO and SELECT statements when using an INSERT
INTO .. SELECT.
As you might have already guessed from the INSERT INTO .. VALUES section,
you do not have to insert the whole row with an INSERT INTO..SELECT. You may load fewer
columns than a whole row of Employee with INSERT .. SELECT. Once again, if we
delete all rows from Emp1, and then execute a statement like this:
INSERT INTO Emp1 (addr, sal)
SELECT address, salary
FROM Employee
This INSERT would leave the other column, empno (of the
Emp1 table), with nulls as shown here:
SELECT *
FROM Emp1
This query produces the following output:
addr sal empno
-------------------- ------------ -----------
123 4th St. 2500.00 NULL
27 Shillingford 3300.00 NULL
95 Oxford Rd 1200.00 NULL
23 Pesterfield Cr 2290.00 NULL
465 Easter Ave 3309.00 NULL
(5 row(s) affected)
In conclusion, you must be careful with the INSERT INTO .. SELECT option,
because, unlike the INSERT INTO .. VALUES option (which inserts one row at a time), you
almost always insert multiple rows, and if types match, the insert will take place whether it makes
sense or not.
|
