SQLConfiguration SQLConfiguration


CRUD Statements  «Prev  Next»
Lesson 8 Inserting data from values in another table
Objective Construct INSERT Transact-SQL statements that retrieve data from another table.

Inserting Data from Values in Another Table

Now that you are familiar with using literal values in your query statements, we begin to focus on query statements that use values from other tables. This is nothing magical, you understand. For the purposes of this lesson, assume that you have brought in a new client: Widget Sales Service (WSS). This client needs to monitor its employees' salary histories. When an employee receives a raise, WSS must store that employee's salary before the raise, along with the date of the raise. WSS has built a SalaryHistory table, shown below, to store this information.

SalaryHistory

LastName FirstName PriorSalary RaiseDate
Smith John 50000 01/01/99
Jones Fred 60000 06/15/99


In order to insert the last name, first name, and prior salary data into this table, WSS will pull this information from their existing Employee table, shown below.

Employee

LastName FirstName Dept CurrentSalary
DuckDonaldENG10000
MouseMickeyMFG20000
MouseMinnieRES30000

Mickey gets a raise

Mickey Mouse has done a good job at WSS, and he's due for a raise from $20,000 to $25,000.

Fist step: insert new salary


The following Transact-SQL statement will insert Mickey’s original salary into the SalaryHistory table.
INSERT INTO SalaryHistory(LastName, FirstName, PriorSalary)  
SELECT LastName, FirstName, Salary
FROM Employee
WHERE LastName = ‘Mouse’
AND FirstName = ‘Mickey’

The SalaryHistory table will now look like this:
LastName FirstName PriorSalary RaiseDate
SmithJohn5000001/01/99
JonesFred6000006/15/99
MouseMickey20000 

Second step: insert raise date

The raise date will not originate from an existing table, but will be entered as a literal value.
It needs to be included as a string literal or by using the GETDATE() SQL Server function, using the following Transact-SQL statement.

INSERT INTO SalaryHistory(LastName, FirstName, PriorSalary, RaiseDate)  
SELECT LastName, FirstName, Salary, GETDATE()
FROM Employee
WHERE LastName = ‘Mouse’
AND FirstName = ‘Mickey’

Inserting Data Values - Exercise

Click the Exercise link below to practice inserting data from values in another table.
Inserting Data Values - Exercise In the next lesson, you will learn how to delete data using values from another table.