I have a #temp table containing all the data that needs to be inserted into a table named Customer. The column names of the two tables are exactly the same, but the data types are different. So I need to cast from one type to another before inserting the data into the Customer table.

The solution has to be implemented for a dynamic condition. i.e, I don't know the column name or data type.

  • 2
    Why not make the datatypes the same? Sep 12, 2014 at 11:25

2 Answers 2

DECLARE @SQL varchar(8000)

SET @SQL = ''

SELECT @SQL = @SQL +  ' CAST(' + COLUMN_NAME + ' as ' + ISNULL(DATA_TYPE + '(' + CAST(CHARACTER_OCTET_LENGTH as varchar(20)) + ') )','') + ' as ' + COLUMN_NAME + ','  FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'YourPermTable'


SET @SQL = 'SELECT ' + @SQL + ' FROM YourTempTable'


Add the insert part and it should do the trick!


I would be writing some code to do this, I'm not sure whether it would be achievable using purely SQL. You'd need to dynamically create a (or some) SQL statements, which may be a question for the chaps over in the programming section, however:

WHERE TABLE_NAME = 'tablename' AND COLUMN_NAME = 'columnname'

Would give you the datatype; you could then select the data from your other table and cast it to the datatype, you can also pull column_name from information_schema but without a table name.. That's another question!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.