Join Adam Wilbert for an in-depth discussion in this video Importing SQL Server objects, part of Migrating Access Databases to SQL Server.
- There are several ways that you can move data from Access into an existing SQL Server database. In this movie I want to make a copy of the products table in the Access KinetEco database, and bring it into the SQL Server version. We'll see a different way of doing this later on in the course but for now let's just go through a simple export and import procedure and take a look at the data type mapping considerations that'll need to be made along the way. First, inside of the Access version I'm going to right click on the products table, I'm going to choose Export, and then I'm just going to export this to a standard text file.
We'll go ahead and choose a location here, so I'll choose the Browse button, and I'll come out to my Desktop, and into the Exercise Files folder, and into Chapter 2, and I've already got one as part of the exercise files called Products.txt, so let's go ahead and just give this a unique name, that way we can keep it separate from the one that we're doing now and the one that I've already given you. So I'm going to call this Products-Export. We're going to leave it as the .txt file extension, and I'll go ahead and press Save here, and then we'll press OK on the Export window.
Then we're going to specify that it's a delimited file. Go ahead and say Next here. I want to make sure that I include the first names on the first row, so you can see those first names or the field columns are named here. We're going to export this as a comma-separated values list, so Comma will be our delimiting character here, and our text qualifier, I want to make sure that that's a double quotation mark. So go ahead and make sure that that's selected from the drop-down list as the double quote. Let's go ahead and say Next here. And then we'll go ahead and export that to our Chapter 2 folder.
So I'll go ahead and say Finish, and that'll place the file on our computer system. Now let's go ahead and go into SQL Server Management Studio and import it into our database. In order to bring it in, I'm going to expand our database folder here inside of SQL Server Management Studio, and I can go ahead and expand the KinetEco-Import file that we've been working with. Let's go ahead and right click on this database, and I'm going to choose Tasks, and then down at the very bottom, Import Data. That'll start up the SQL Server Import and Export Wizard.
We can go ahead and read some information about how the wizard works here, and then press Next. On this screen we get to choose the data source. And we have a drop-down list here that have lots of different drivers for how we want to select our source. We can choose from the .net Framework or we can choose a flat file source, which is what we will use. We also have some options here to pull directly from Microsoft Access. And you might think, okay, this is probably the way I want to go, right? But if you select this option here and then press Browse to try and find the file that you want, you'll notice that it actually only represents the MDB files, which are the old style Access database, before 2007.
All the new, recent databases use the .accdb file format, and so this database file format won't work here. Let's go ahead and press Cancel here. So what I want to do is pull in that text file. So I'm going to go back up here to this drop-down list and choose a flat file source, that will be a flat file like a text file or something like that. Then in the file name, we get to press this Browse button here, and we'll find that file on our Desktop, inside of Exercise Files, Chapter 2, and you can either use the one that I provided previously or the one you just exported.
I'll go ahead and choose the one that I provided earlier, this Product.txt file. They should be identical, though. Let's go ahead and say Open to that. Next we want to check some settings here. If our table includes unicode characters I'd want to make sure that this check box is turned on. It doesn't, so I'm going to go ahead and leave this off here. The format is delimited, but we can also choose if we specified it was a fixed width file format instance we could change it there. But again I'm going to leave it as delimited. The text qualifier we did specify, so instead of none, I'm going to type in just a single double quote character here.
The header row, which we do have, has a delimiter as well, and this right here is code that says CR and then LF inside of the curly brackets. That stands for Carriage Return and then Line Feed, which is terminology that actually comes all the way back from typewriters. When you had to go to a new line on a typewriter, you had to push the printhead, or the carriage, all the way to the left hand side of the piece of paper, that's the carriage return. And then you had to move the paper up, which was the line feed. So actually, at the end of our text file here we have a carriage return and a line feed, which specifies the end of our header row.
If we wanted to skip our header row we could type in the number here, but we're going to leave this as 0. And then make sure that the check box for column names are in the first data row, make sure that's turned on. Next, over here on the Data Source tab, we can review our columns to make sure that things are looking okay, and if you see any double quotes in here, you want to go back to this general tab and just make sure you have a double quote in as the text qualifier. I don't see that here showing up on my data so that looks good here. We can review that the comma is the column delimiter and we have a row delimiter of also a carriage return followed by a line feed.
Then we'll go into the advanced section here where we can just start setting our data types. When we export this data file, everything comes in and it doesn't know what it is. It just says that everything is a string data type or text. For instance, if I click on Category, it says string of 50 characters. And Power is also the string of 50 characters. In order to start the process of generating our data types, I can press this Suggest Types button down here, in which case SQL Server is going to go through and look at the data and it's going to make its best guess as to what the data types should be.
Let's go ahead and say OK to these default options here, and it'll go through and make some changes. Take a look, Product ID is now a string character of 8. So it found that the largest value was eight characters long and decided that was a good fit. Category is a string of 18 characters, Name is 48 characters, Power it decided was a floating point number, Footprint, also floating point. And we can go through some of these other ones and see some different options here. Let's go ahead and make some changes here because I know some of the data types are a little bit different than what it suggested here.
For instance, instead of a float, I'm going to use this drop-down list, and I'm going to choose the decimal data type here. And the same thing for Footprint. I'm going to change it from a floating point number to a decimal number. So it's no longer an imprecise floating point, it's an exact numeric, which is a decimal. Let's go ahead and say Next, and we'll go to the next screen here. On this screen we get to specify where to copy the data to. And again, we have this destination field. I'm going to use this drop-down list, and we can use the SQL Server Import and Export Wizard to move data all over the place. In this case I actually want to bring it into SQL Server so I'll scroll down to the bottom and choose the SQL Server Native Client 11.
Go ahead and choose that here, it asks me which server name, and I only have one server on my computer, so make sure that this is the name that represents the name that you typed in during installation. We'll use Windows Authentication to log in to the server here, so it'll log in with my current username, and it automatically populated the database it's going to go into here. Let's go ahead and say Next. Then it tells us the name of the data table. It's going to be this Products table here, it's just grabbing the name from the text file that we're importing here. Gives me a reminder of the source path where we're coming from, and I can go ahead and press the Edit Mappings button to finalize the data mappings that are going to get applied when it comes into SQL Server here.
So here are the data types that it's going to convert to, and I can make changes here. So instead of the ProductID being a varchar, maybe I just want to make this a regular char data type. So when I bring it in, it'll be a char, and instead of 8 characters I want to give myself some padding so I'll say 10 characters. Category, I'll change that to a full 20 characters, and we might as well just round this one up to 50 as well. For the Power, right now it's set as a decimal with a precision of 28, that means it's going to have 28 digits. That's a little bit for my needs, so what we're going to say is that this number is going to have 10 digits, but then three of those are going to follow the decimal point.
So essentially we'll have seven digits, and then a decimal point, and then three more digits. We'll do the same thing for Footprint, so I'll specify, we'll allow up to 10 digits total, with three of them being allocated to the decimal point. And for some reason it's not letting me type in a 3 for the scale, so let's see if we can reset it by changing this to numeric and then back to decimal. There we go, now we have a number here. Not sure why that happened, but okay. Now we can type in the scale of 3. The ManufacturingCost and the SuggestedRetail, we know those aren't just decimal values, those are money data types, so I'm going to go ahead and change that to smallmoney.
I'll scroll up and find that there. Go ahead and change this one to smallmoney as well. If I scroll down on the list here we can specify that our TargetDomestic, TargetCorporate, and TargetPersonal, it's decided that these are going to be smallnts, and actually, these are just bit values. These are just a yes/no data type inside of Access, so all we need to do is store these as bits. Go ahead and change all three of those. Let's go ahead and say OK to finalize these mappings here. And we'll go ahead and say Next. We're going to get a couple of warnings here, because we are converting some data types, for instance, this money data type is being converted here, and also the bits, so there is a potential for data loss if we're not careful with the imported data and what data type we're converting it into.
In this case I'm pretty confident that we've got the right ones, so I'm going to go ahead and say Next here. And it says run immediately, sounds good to me, let's go ahead and press Finish, which wasn't apparently immediate, we'll press Finish one more time. If we get a whole bunch of success lines here that means everything was imported without any issues. So we can go ahead and close this out. If you do have any issues you'll see a message link over here, and you can click on a hyperlink to maybe get some information about what might have gone wrong. Let's go ahead and close this Import and Export Wizard out and take a look in our data tables over here in the CnetEco-Import database.
I'll expand this open, and here is that brand new products table. Being expanded I can see all of my columns here. I want to see the data, I can right click on it and choose Select Top 1000 Rows. That'll pull out the data that's in this data table. And here is the SQL code that generates this query here and down at the bottom, if I click here and drag up, we can see some of the data here. If I look through I shouldn't see any data that's being truncated. You can see our Power and Footprints are now allowing up to three digits after the decimal. Manufacturing and SuggestedRetail costs are data values that represent money, so they have two decimal places here.
The bit values are 0 and 1, so that's our yes and no field back in Access. And it looks like everything came in properly. So by exporting the table from Access as a simple comma delimited text file, we're able to bring in both the structure of the table with the data type 3 maps, and the data itself into SQL Server using the Import command.
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