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.

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.

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

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

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’

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.