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.
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
> 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
> 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"
> 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
> Click on Close
Use a TSQL script to move the data from the PostcodeImport table to the Postcode table
insert intoPostcode (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 =‘ ‘ then–1
when p.Series is null then–1
when p.Series =” then–1
else p.Series
end
as int )
from PostcodeImport p