Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
It's a very common need that when creating a database we need to import some data that exists somewhere else. That could be in an Excel file, a CSV file, an Access database, even another instance of SQL Server or a previous version of it, or we can do all of these things using the SQL Server Import/Export Wizard. Under my installed programs for SQL Server 2008 R2, I actually have this link, Import and Export Data 32-bit and 64-bit, I have two versions. Now the reason for that is that while my SQL Server instance is installed as 64-bit, some of the data sources that I might want to import from or export to aren't 64-bit so I need to talk to them using the 32-bit version, and basically if one of them doesn't work try the other.
So I've opened the wizard here. It just gives me a greeting message that it will copy between databases, spreadsheets, text files, and so on. I'm going to click Next because I need to give it two pieces of information: where am I copying from and where I'm copying to. Now when I click this top level drop- down box I can see I have a bunch of selections here. I've got the ability to talk to Oracle, to talk to Microsoft Excel, to talk to Access SQL Server, and depending which one I pick, I may have to provide other really simple information such as selecting from Excel, give it the file path, and say which version it is.
If how many other hand I'd selected to import from Oracle, we have all the .NET Framework Data Provider source and I have to provide things like the password and the user ID and the data source information. Essentially once this is declared then we just double it up about and say where it's actually going to. It will then step us through the process. I'm going to show a couple of examples of that in just a minute. Now one thing to show is though even that we could copy from SQL Server itself using this method, it's not the suggested way to do it. In fact if I click the Back button to that first greeting page, it does tell you or remind you to move or copy databases and their objects from one SQL Server instance to another, cancel this wizard and use the Copy Database Wizard instead, I'll show you where that is in just a minute.
And this is the last thing before I get started. I would say that this same thing is available through SQL Server Management Studio. So if you are in the middle of designing your databases, say my TwoTreesTest, and I know that I want to import some information here, if I right-click my database, come down to Tasks, which shows me a whole bunch of different things. Take it offline, shrink it, back it up, restore it. But down here I have Import Data and Export Data. Well, not surprisingly clicking those is just going to open up exactly the same wizard that will allow us to step through that process.
Again we're getting that same message that to use the Copy Database Wizard if we want to copy SQL Server databases and you won't find that under any of the tools options. It is again under one of the tasks. Instead of selecting the task to import and export, I'd clicked Copy Database. That would open up the Copy Database Wizard. I'm not going to use that one right now. In fact, I'm basically going to shut that down and show you one more thing here. And this is just the phrase that it uses on the welcome page of the wizard.
This wizard helps you to create simple packages. Now what does this mean? Well we're really using a part of SQL Server called SQL Server Integration Services, which can get much more involved than just a simple import, and if you're looking at setting I'm not just a one-off import but a scheduled, repeatable, automated import that happens twice-a-week or even twice-a-day, you're going to be looking at using SQL Server Integration Services. And I'll be covering that towards the end of this course. But before we get to that let me walk through a few examples of just using this Import/Export Wizard.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70047 Viewers
80 Video lessons · 127437 Viewers
52 Video lessons · 62372 Viewers
59 Video lessons · 48076 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.