When the Microsoft SQL Server Import and Export wizard is not enough
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.
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 ptSQL Server