Working with Different Datatypes

The previous three lessons covered inserting, updating, and deleting rows using values pulled from another table. This lesson covers a snag that can show up in any of them: the source and destination columns do not always use the same datatype, and sometimes the destination table has no column suited to the incoming data at all. SQL Server 2025 gives you two ways to handle this: convert the incoming data on the fly during the statement, or extend the target table's schema to accommodate it.

SQL Server performs implicit conversions automatically for compatible types, such as INT to BIGINT or VARCHAR to NVARCHAR, but relying on that can mask dirty data and shows up as a CONVERT_IMPLICIT warning in your execution plan. Explicit conversion in the SELECT or SET list is the safer habit, and four functions cover almost every case:

WSS also maintains a separate table that tracks raises by department, so management can check that no single department is being favored over the others. Each time a member of any department receives a raise, that employee's last name and department code are retrieved from the Employee table and inserted into the DepartmentRaise table.

Before writing the INSERT statement, there is a snag that must be dealt with. Employee.DepartmentCode is a char(3) column, a holdover from an older system that stored every code as text, while DepartmentRaise.DepartmentCode is a plain int. When inserting data, the datatype of the value being inserted must always be compatible with the datatype of the column receiving it. Here, the values line up (both tables are using the same numbering scheme, just stored differently), so a straightforward conversion will close the gap.

The CAST function is the ANSI-standard way to perform that conversion explicitly:

CAST (expression AS datatype)

• expression: the data you wish to convert.
• datatype: the target datatype you wish to convert the expression to.

Writing the conversion explicitly, rather than letting SQL Server attempt it implicitly, documents the intent and fails cleanly if the source data ever stops being purely numeric, rather than relying on the engine to guess correctly. Mickey Mouse just got a raise, so his last name and department code need to move from Employee into DepartmentRaise:

INSERT INTO DepartmentRaise (LastName, DepartmentCode)
SELECT LastName, CAST(DepartmentCode AS int)
FROM Employee
WHERE LastName = 'Mouse'
  AND FirstName = 'Mickey';

As a result of the statement above, DepartmentRaise now looks like this:

TRY_CAST tells you whether a value converts, but it cannot tell you whether a value that does convert is actually shaped the way your business rules expect. TRY_CAST('12' AS int) succeeds and returns 12, even though a WSS department code is always supposed to be exactly three digits. SQL Server 2025's native regular expression functions, REGEXP_LIKE chief among them, can check the shape directly, before a conversion is ever attempted:

SELECT LastName, FirstName, DepartmentCode
FROM Employee
WHERE REGEXP_LIKE(DepartmentCode, '^[0-9]{3}$') = 0;

This returns every Employee row whose DepartmentCode is not exactly three digits: a code with letters in it, a code that is too short, or one that is too long. REGEXP_LIKE(input, pattern) returns a Boolean, so it drops directly into a WHERE clause as either a pre-flight check like this one, or as a filter inside the INSERT statement itself, alongside or instead of TRY_CAST.

Sometimes you want to find and set aside the rows that fail conversion, rather than just skipping them. Wrapping the candidate conversions in a WITH clause lets you compute each one once and test the results, instead of repeating TRY_CAST and TRY_CONVERT a second time in the WHERE clause:

WITH ConversionCheck AS (
    SELECT
        s.RawOrderID,
        s.RawTotal,
        s.RawOrderDateString,
        TRY_CAST(s.RawTotal AS DECIMAL(12, 2)) AS ParsedTotal,
        TRY_CONVERT(DATE, s.RawOrderDateString, 101) AS ParsedDate
    FROM Staging.RawOrderImport AS s
)
INSERT INTO Staging.ImportErrors (RawRowID, BadAmountValue, BadDateValue, ErrorReason)
SELECT RawOrderID, RawTotal, RawOrderDateString, 'Data type conversion failed for DECIMAL or DATE'
FROM ConversionCheck
WHERE ParsedTotal IS NULL
   OR ParsedDate IS NULL;

Rows that would otherwise silently disappear under a plain TRY_CAST-based filter land here instead, with a reason you can act on.

When moving data from a staging table, where columns are often stored as loosely typed text, into a strongly typed production table, a single bad row under plain CAST or CONVERT fails the whole statement. Wrapping TRY_CAST or TRY_CONVERT in COALESCE or ISNULL lets the load finish while substituting a default or NULL for the rows that do not convert:

INSERT INTO Sales.Orders (OrderID, OrderDate, TotalAmount, IsPriority)
SELECT
    TRY_CAST(s.RawOrderID AS INT),
    TRY_CONVERT(DATE, s.RawOrderDateString, 101),
    ISNULL(TRY_CAST(s.RawTotal AS DECIMAL(12, 2)), 0.00),
    CASE
        WHEN s.PriorityFlag IN ('Y', 'Yes', '1', 'True') THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
    END
FROM Staging.RawOrderImport AS s
WHERE TRY_CAST(s.RawOrderID AS INT) IS NOT NULL;

The style code 101 converts an MM/DD/YYYY string into DATE, and the trailing WHERE clause filters out any row whose primary key itself fails to convert, since no default value is safe to substitute there.
If a source column has region-specific formatting, such as a currency symbol or a date written day-first, TRY_PARSE understands that where TRY_CAST does not:

UPDATE tgt
SET
    tgt.UnitPrice = TRY_PARSE(src.FormattedEuroPrice AS MONEY USING 'de-DE'),
    tgt.LastRestocked = TRY_PARSE(src.UKDateString AS DATE USING 'en-GB')
FROM Production.Inventory AS tgt
JOIN Staging.EuropeanSupplierFeed AS src
  ON tgt.SKU = src.SKU
WHERE TRY_PARSE(src.FormattedEuroPrice AS MONEY USING 'de-DE') IS NOT NULL;

Sometimes the mismatch is not just a different datatype, the target table has no column capable of holding the incoming data at all: JSON documents, spatial coordinates, or vector embeddings being inserted into a table that only has basic scalar columns. SQL Server 2025 gives you three ways to handle that.

Serialize into an existing text column. If you cannot alter the target schema, convert the complex value to text, or pack several unmapped source columns into one JSON object on the way in:

INSERT INTO Production.Products (ProductID, ProductName, ExtendedAttributesJson)
SELECT
    s.ProductID,
    s.ProductName,
    JSON_OBJECT(
        'WeightKg': TRY_CAST(s.Weight AS DECIMAL(8,2)),
        'Dimensions': s.DimensionsText,
        'DiscontinuedDate': TRY_CAST(s.DiscDate AS DATE),
        'IsHazardous': TRY_CAST(s.HazardFlag AS BIT)
    )
FROM Staging.VendorCatalog AS s;

Add the column first with ALTER TABLE. If the new datatype deserves a permanent, properly typed home, including SQL Server 2025's native JSON and VECTOR types, add a nullable column before running the INSERT or UPDATE:

ALTER TABLE Production.Products
ADD ProductMetadata JSON NULL,
    SearchEmbedding VECTOR(1536) NULL;

A VECTOR column holds a fixed-length array of floating-point numbers, most often an AI-generated embedding, and SQL Server 2025 can compare them directly with the VECTOR_DISTANCE function once they are populated.

Use SQL_VARIANT for a column that holds more than one base type. If a single column genuinely needs to store different base datatypes row by row, one row an INT, the next a DATETIME2, the next a DECIMAL, define that column as SQL_VARIANT. You can insert any supported type directly into it and recover the original base type later with SQL_VARIANT_PROPERTY(ColumnName, 'BaseType').

In the next lesson, you will learn how to use subqueries in your queries.