| 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 Table
| LastName | FirstName | DeptartmentCode | CurrentSalary |
| Duck | Donald | ENG | 10000 |
| Mouse | Mickey | MFG | 20000 |
| Mouse | Minnie | RES | 30000 |
DepartmentRaise Table
| 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
In SQL Server 2019, the "CAST" function serves as a powerful and versatile mechanism to convert data from one datatype to another,
ensuring compatibility across different datatypes. This function becomes particularly indispensable when dealing with operations that
necessitate a uniform datatype, such as comparisons, calculations, or when inserting data from one table into another with differing datatypes.
CAST (expression AS datatype)
- expression: The data that you wish to convert.
- datatype: The target datatype to which you wish to convert the expression.
Utilization of the CAST Function:
The CAST function is employed to explicitly convert an expression from one datatype to another. This is vital in scenarios where implicit conversions might not work or could potentially lead to data loss or precision issues. The explicit nature of the CAST function ensures clarity and precision in the data conversion process, mitigating the risks associated with automatic or implicit datatype conversions.
Example:
Consider a table named "Sales" with a column "TotalAmount" of datatype VARCHAR. To sum the values in "TotalAmount", a conversion to a numeric datatype is required. This can be achieved using the CAST function as demonstrated below:
SELECT SUM(CAST(TotalAmount AS DECIMAL(10, 2)))
FROM Sales;
In this example, the values in the "TotalAmount" column, originally of VARCHAR datatype, are explicitly converted to DECIMAL using the CAST function, facilitating the subsequent SUM operation.
Key Considerations:
- Data Integrity: Before performing a conversion, ensure that the data in the source column is valid and compatible with the target datatype to prevent errors during the conversion process.
- Precision and Scale: When converting to decimal or numeric datatypes, it is crucial to specify the precision and scale to accommodate the data without loss of information.
- Error Handling: Implement error handling mechanisms to gracefully handle any potential issues that might arise during the conversion process.
- Performance Implications: Be mindful of the potential performance implications, especially when converting data in large datasets or in performance-critical operations.
To summarize, the CAST function in SQL Server 2019 stands out as an indispensable tool for converting data between different datatypes, ensuring compatibility and facilitating smooth operations across diverse data types. It guarantees explicit and precise conversions, contributing to the robustness and reliability of SQL queries and operations.
When the datatypes are not compatible, you can convert the data with the CAST SQL Server function.
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.