data type conversion

Question

Post reply

data type conversion

deebabat

Ten Centuries

Points: 1125
More actions
April 24, 2012 at 9:17 am

#255414

I am trying to insert into a table by selecting from another table but one of the columns in the two tables have different data type. please how do I convert the data type of that particular column from VARCHAR to match the destination table data type which is INT.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Eugene Elutin

SSC Guru

Points: 59322

April 24, 2012 at 9:19 am

#1478280

use CONVERT or CAST function

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

deebabat (4/24/2012)

I am trying to insert into a table by selecting from another table but one of the columns in the two tables have different data type. please how do I convert the data type of that particular column from VARCHAR to match the destination table data type which is INT.

If you have a table with a column of type INT and you attempt to insert data from another column of type VARCHAR SQL Server will attempt to convert VARCHAR to INT as part of insert operation. This is known as an implicit data type conversion and is only done because SQL Server provides a clear path to cast a VARCHAR to an INT, however not all data type pairs can be casted from one to the other.

Even though VARCHAR to INT has a valid cast-path, the VARCHAR data must still be castable to INT, i.e. all the strings must qualify as integers. If some of the VARCHAR data cannot be converted to INT an error is thrown.

Note that it is a best practice to explicitly cast your VARCHAR data to INT as part of your INSERT/SELECT using CAST or CONVERT, but it is not technically necessary to achieve the insert and those functions will not help your cause if you have VARCHAR data that does not qualify as an INT.

Are you receiving an error when you attempt to insert data?

This is what a query might look like that explicitly casts varchar data to int:

INSERT INTO dbo.some_table



        (

         some_column -- is of type INT

        )

        SELECT  CAST(some_other_column AS INT) -- some_other_column is of type VARCHAR(10)

FROM dbo.some_other_table;

Example, this SQL will throw an error despite the use of CAST:

SELECT CAST('a string' AS INT) AS some_value

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 3

opc.three (4/24/2012)

deebabat (4/24/2012)

I am trying to insert into a table by selecting from another table but one of the columns in the two tables have different data type. please how do I convert the data type of that particular column from VARCHAR to match the destination table data type which is INT.

If you have a table with a column of type INT and you attempt to insert data from another column of type VARCHAR SQL Server will attempt to convert VARCHAR to INT as part of insert operation. This is known as an implicit data type conversion and is only done because SQL Server provides a clear path to cast a VARCHAR to an INT, however not all data type pairs can be casted from one to the other.

Even though VARCHAR to INT has a valid cast-path, the VARCHAR data must still be castable to INT, i.e. all the strings must qualify as integers. If some of the VARCHAR data cannot be converted to INT an error is thrown.

Note that it is a best practice to explicitly cast your VARCHAR data to INT as part of your INSERT/SELECT using CAST or CONVERT, but it is not technically necessary to achieve the insert and those functions will not help your cause if you have VARCHAR data that does not qualify as an INT.

Are you receiving an error when you attempt to insert data?

This is what a query might look like that explicitly casts varchar data to int:

INSERT INTO dbo.some_table
( some_column -- is of type INT ) SELECT CAST(some_other_column AS INT) -- some_other_column is of type VARCHAR(10)
FROM dbo.some_other_table;

Example, this SQL will throw an error despite the use of CAST:

SELECT CAST('a string' AS INT) AS some_value