CRUD Statements  «Prev  Next»
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
DuckDonaldENG10000
MouseMickeyMFG20000
MouseMinnieRES30000

DepartmentRaise


Last Name DepartmentCode*
Smith452
Jones231
Nelson300
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.