Dynamic Casting of data from one table to another

问题:

I have a store procedure which is fetching column details of a table. Say this table name is Invoice table. I have another table, Invoice_Staging table, which has data in varchar format. Now I wish to fetch data from Invoice_staging, cast the values as per data types mentioned in Invoice table and do a bulk insert into Invoice table. Now, by chance, there is a value whose Casting isn't possible, in this case, I don't want my bulk insert to fail. Rather make that particular field NULL and insert it in the Invoice table. I am stuck with fetching the data and casting them as required. Kindly show me a way, which could throw a lime light on my issue. The stored procedure is as follows -

create PROCEDURE usp_stagingToProduction
@staging_tableName varchar(200),
@master_tableName varchar(200)  

AS BEGIN SET NOCOUNT ON;

Declare @count int, -- for counting the total number of records in a table 
@RowCount int,
@columnName varchar(200),
@columnDataTypeName varchar(200),
@maxLength int,
@precision int,
@scale int,
@isNullable int,
@primaryKey int,
@sql varchar(5000)
Set @sql = ''

    create table #MyTemporaryTable
    (
        ColumnName varchar(500),
        DataType varchar(100),
        MaxLength int,
        [Precision] int,
        Scale int,
        IsNullable bit,
        PrimaryKey bit,
        flag bit default 0,
        id int identity(1,1)
    )

    insert into #MyTemporaryTable
    SELECT 
            c.name 'ColumnName',
            t.Name 'DataType',
            c.max_length  'MaxLength',
            c.precision 'Precision',
            c.scale 'Scale',
            c.is_nullable 'IsNullable',
            ISNULL(i.is_primary_key, 0) 'PrimaryKey',
            0
        FROM    
            sys.columns c
        INNER JOIN 
            sys.types t ON c.user_type_id = t.user_type_id
        LEFT OUTER JOIN 
            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
        LEFT OUTER JOIN 
            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
        WHERE
            c.object_id = OBJECT_ID('Invoice')

        -- select * from #MyTemporaryTable  -- To display the content of temporary table


        declare @column varchar(500)
        declare @datatype varchar(500)
        declare @id int
        Set @sql = @sql + 'Insert Into ' + @master_tableName
        Set @sql = @sql + ' Select '
        while exists (select Top 1 * from #MyTemporaryTable where flag  = 0)
        BEGIN
            Select @id = id, @datatype = DataType, @column = ColumnName from #MyTemporaryTable Where flag = 0
            Set @sql = @sql + ' Cast(' + @column + ' As ' + @datatype + '), ' -- building the cast query


            update #MyTemporaryTable
            set flag = 1
            where id = @id

            -- Select @sql -- to see whats happing
        END
        Select @sql = SubString(@sql, 1, LEN(@sql) - 1)
        Set @sql = @sql + ' From' +' ' + @staging_tableName  -- #MyTemporaryTable'

        Select @sql -- just to display query. nothing to do anything with the logic 

        -------------------
        select @count = count(*) from @staging_tableName -- getting column count from staging table
        declare @counter as int
        set @counter = 1
        if(@count >0)
             -- data found in staging table
             -- logic here for casting and inserting the data to the table

        else

END GO

--usp_usp_stagingToProduction 'Invoice'