Join Adam Wilbert for an in-depth discussion in this video Mapping source and target data types, part of Migrating Access Databases to SQL Server.
- In the Migration Assistance, we also have fine control over how our Data Types in Access map to the Data Types in SQL Server. Let's take a look at those options by coming up here to the Tools menu, and then coming down to the Default Project Settings again. Once again just like in the last movie, we'll choose the Target Version of SQL Server as SQL Server 2014. Then, I'll come down here to the very bottom on the left and press this button that says Type Mapping. On the left, we have the Source Type, and these are the Data Types that are coming out of Access. On the right, we have the Target Type.
These are the Data Types that are going to get converted into, during the Migration Process, into SQL Server. So, we can see that the boolean values are going to get converted into bit in SQL Server, and byte will get converted into a tinyint. Now for the most part, SSMA is fairly generous with the conversion, opting the up size many of the Data Types. For instance, earlier we saw that the Access Data Time Data Type only store time down to the whole second, which is equivalent to the SQL Server Small datetime. Here, inside of the Project Settings, I could see that the datetime filed here, is going to get converted into datetime2, which will allow us to store fractions of a second in SQL.
We can also see that the text and Memo Data Types down here are being converted into the nvarchar varieties. These will allow you to store Unicode characters. Now, if you know that you're not going to be using Unicode, then we can go ahead and change these Mappings here. I'll go ahead and select this line that says text, and I'll press Edit here. Then, in the Target Type drop-down list, I'll go ahead and choose varcharlength instead. Then, we can either specify an exact length with this number here, or if I turn off this check-box, it'll make it a variable length based off of the Source Data Type.
I'll go ahead and leave this turned off and say OK, and I see that updates over here in the Mapping Table. Finally, one other conversion that I want to point out is this decimal Data Type here. Decimal is an exact numeric Data Type, but it's going to get converted into an approximate floating point number in SQL Server. If you know the format of your Data meets a certain precision in scale, you can maintain the exact numerics by editing the Mapping. Once again, I'll select this line here and say Edit. Then, instead of a floating point number, I'm going to go ahead and change it to a decimal with a specific precision and scale.
Then, I'll set the precision here to 10, so that this number will include 10 digits, and the scale will be three. So, three of those 10 will be after the decimal. We'll go ahead and say OK, and that updates the Mapping Type here. When we're done, we'll press the Apply button, and then OK to make those changes to our environment. So, that's how you would modify the Default Data Type Mappings. By knowing the Data that is in your own Database, you can make better decisions on how the Data should be translated during the Migration from Access to SQL Server.
Watch this course to learn how to leverage what you already know to take that next step. Adam Wilbert reviews the basics of SQL Server Management Studio (SSMS) and introduces two workflows for migrating Access data: one using a simple export/import process and the other using the free SQL Server Migration Assistant (SSMA). He also shows how to convert Access to SQL data types, link Access to SQL data via an ODBC connection, and address special concerns for Access 2010 data.
- Why upgrade?
- Importing a SQL database
- Exploring data types and server objects
- Installing SSMA
- Mapping source and target data types
- Using the Migration Wizard
- Linking to migrated tables in SSMA
- Working with views and stored procedures
- Linking Access to SQL Server databases