When importing data from Microsoft Access to Microsoft SQL Server you can use the Microsoft SQL Server Import and Export wizard, but some times you can not use this wizard because values in a column can’t be casted to the correct format.

 

Like a “Series” column that contains the values (1,2,3 and a ‘ ‘) to a int column. To quickly import the data, I created a temp table, with the exact same columns accept for the “Series” column. Edit the mappings and source table in the import and export wizard.

 

image

 

insert into Postcode (Postcode, Straatnaam, Plaats, RangeStart, RangeEind, PostcodetypeId)
select distinct pt.Postcode, pt.Straatnaam, pt.Plaats, pt.RangeStart, pt.RangeEind,
cast(
    case
        when pt.PostcodetypeId = ' ' then -1
        when pt.PostcodetypeId is null then -1
        when pt.PostcodetypeId = '' then -1
        else pt.PostcodetypeId
    end
as int)
from PostcodeTemp pt

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.