Lesson 11 | Working with different datatypes |
Objective | Insert a different datatype into a table. |
Working with Different Datatypes in SQL Server
When you insert or update your table using data from other tables, you will often need to rectify values that use different datatypes. These tables are shown below:
Another request from WSS
WSS also maintains a separate table that tracks raises by department, in order to make sure that some departments aren’t favored over
others. Each time a member of any department receives a raise, the employees last name and department code are retrieved from the Employee
table and inserted into the DepartmentRaise table.
Employee
LastName | FirstName | DeptartmentCode | CurrentSalary |
Duck | Donald | ENG | 10000 |
Mouse | Mickey | MFG | 20000 |
Mouse | Minnie | RES | 30000 |
DepartmentRaise
Last Name | DepartmentCode* |
Smith | 452 |
Jones | 231 |
Nelson | 300 |
Int Datatype |
Incompatible datatypes
Before proceeding to write an INSERT statement, there is a new snag that must be dealt with. The DepartmentCode column in the Employee table
is a char(3) datatype, while the DepartmentCode column in the DepartmentRaise table is an int datatype.
When inserting data, the datatype of the inserted data must always be compatible with the datatype of the column that is being retrieved.
CAST SQL Server function
When the datatypes are not compatible, you can convert the data with the CAST
SQL Server function. The CAST
function follows
this syntax:
NINAH: new image goes here: castfunction.gif
With this in mind, the following statement will retrieve Mickey’s last name and department code from the Employee table and insert this
data into the DepartmentRaise table:
INSERT INTO DepartmentRaise(LastName, DepartmentCode)
SELECT LastName, CAST(DepartmentCode AS char(3))
FROM Employee
WHERE LastName = ‘Mouse’
AND FirstName = ‘Micky’
Updated table
As a result of the commands issued above, the DepartmentRaise table will now look like this:
Last Name | DepartmentCode* |
Jones | 231 |
Smith | 452 |
Nelson | 300 |
Mouse | RES |
* Int Datatype |
In the next lesson, you will learn how to use subqueries with your query statements.