There are several ways to import data from Microsoft Office Access to Microsoft SQL Server, but I decided to import de data from Microsoft Access into a Microsoft SQL Server table with "the same" columns and types and made all columns nullable to avoid import errors and then created a TSQL script to move the data to the destination table with different columns and types. This was, because I know a lot about Microsoft SQL Server and less about Microsoft Access.

Table in Microsoft Access
image

Import Table in Microsoft SQL Server
create table dbo.PostcodeImport
(
 
Id int not nullidentity(1,1)primary key,
 
Postcode varchar(6) null,
 
Series varchar(1) null,
 
[Huisnummer van] int,
 
[Huisnummer t/m] int,
 
Plaats varchar(24) null,
 
Straat varchar(28) null
)
go

To import the data in the "PostcodeImport" table:
> Open Microsoft SQL Server 2008 Management Studio
> Right click you’re database > Tasks > Import Data …
> On SQL Server Import and Export Wizard screen 1 click Next
> On SQL Server Import and Export Wizard screen 2 choose Data source: Microsoft Access
    Then click on Browse to select the Microsoft Access file
image
> On SQL Server Import and Export Wizard screen 3 choose Destination: SQL Server native Client 10.0, the enter you’re SQL Server name.
> Enter the database name
image
> On SQL Server Import and Export Wizard screen 4 choose "Copy data from one or more tables or views"
> On SQL Server Import and Export Wizard screen 5 choose Source table "Postcode" and destination table "PostcodeImport"
image
> We don’t have to edit the column mappings, because the columns have the same names and types
> Click on Next
> On SQL Server Import and Export Wizard screen 6 click Next
> On SQL Server Import and Export Wizard screen 7 click Next
> On SQL Server Import and Export Wizard screen 8 click Finish
image 
> Click on Close

Use a TSQL script to move the data from the PostcodeImport table to the Postcode table

insert into
Postcode (Postcode,Straatnaam,Plaats,RangeStart,RangeEind,PostcodetypeId)
select distinct p.Postcode,p.Straat,p.Plaats,p.[Huisnummer van],p.[Huisnummer t/m],
cast (
  
case
        when
p.Series =‘ ‘ then1
        when p.Series is null then1
        when p.Series =then1
        else p.Series
    end
as int
)
from PostcodeImport p

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.