SQL Server – How to Insert Data from One Table to Another After Casting Values

bulk-insertcastinsertsql server

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.

Best Answer

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 = SUBSTRING(@SQL,1,LEN(@SQL)-1)

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


PRINT @SQL

Add the insert part and it should do the trick!

Related Solutions

SQL Server – Update Tables Based on Survey Answers

I would say this is a pretty reasonable solution. You might consider adding an "active" flag or something similar so you can deactivate certain rows from your AffectedData table without deleting them. Also you will need to make sure that any solution you have takes into account the possibility of multiple entries in the metadata table. Either by putting a primary key on it that restricts them or by using a loop or batch solution when you are creating your dynamic SQL.

Sql-server – Remove while loop from query inserting x values

I believe I have solved it with the following cte and cross apply. Thanks to Aaron for letting me think it was simpler than I had it.

;WITH cte AS
(
SELECT 
    D.Id, D.WidgetTypeId
FROM 
    @Widgets R 
    CROSS APPLY 
    (
        SELECT 
            TOP(R.Required) *
        FROM 
            WidgetOrder A
        WHERE 
        A.OrderId IS NULL AND A.WidgetTypeId = r.WidgetTypeId) D
)
UPDATE A SET OrderId = 100 FROM WidgetOrder A JOIN cte ON A.Id = cte.Id

Best Answer

Related Solutions

SQL Server – Update Tables Based on Survey Answers

Sql-server – Remove while loop from query inserting x values

Related Question