| 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 arent 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.
|LastName ||FirstName ||DeptartmentCode ||CurrentSalary |
|Last Name ||DepartmentCode* |
| Int Datatype|
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
NINAH: new image goes here: castfunction.gif
With this in mind, the following statement will retrieve Mickeys 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))
WHERE LastName = ‘Mouse’
AND FirstName = ‘Micky’
As a result of the commands issued above, the DepartmentRaise table will now look like this:
* Int Datatype
In the next lesson, you will learn how to use subqueries with your query statements.