We have now talked about inserting data into SQL Server with functionality from Excel in both Quick and Simple Inserts and More Fun Inserting Data Using Values. Some of you are probably sitting back and saying “That’s just too much work. Give me something that will walk me through it and not have to remember functionality from Excel.”
Have I got the thing just for you today! This time, we’ll go thru pushing the data into our SQL Server using a wizard.
No, my friends. This time we’re talking about the wizards built into SQL Server itself. This one is called simply the “Import Data Wizard”
So, in our tool of choice – I’m using SQL Server Management Studio (SSMS) for this – we will do the following:
- Right-click on the database we want to push the data into.
- Left-click on Tasks
- Left-click on “Import Data”
Or shown in visual format…
Click next on the Welcome screen.
On the next screen, you’ll change the following:
- Data Source should be “Microsoft Excel”
- Provide the path to the spreadsheet
- Change the Excel version to be “Microsoft Excel 2007-2010.”
- Make sure the “First Row has column names” is selected
- Click Next
For our destination, you’ll change the following:
- Destination to “Microsoft OLE DB Provider for SQL Server”
- You should make sure that the other fields are set correctly, such as the database that you’ll be importing data into.
On the next screen, we can tell it to “Copy data from one or more tables or views”
You’ll know things are good when you get this next screen, showing that we’re copying one tab from Excel into a table in SQL Server:
Click Next
Click Run Immediately.
This should end up in the following screen – showing that all 1100 rows were inserted successfully into a table.
Yes, that is a lot of steps. Yes, you are pushing data into a table that you didn’t specify however SQL Server sees it (there are ways to clean that up but I thought I’d keep it “simple” for this time out).
Now, after showing all of the steps, there are things to remember
- You may not have a “simple” spreadsheet that can be imported into SQL Server easily. This means working with the inner workings of the SSIS packages that are used behind the scenes for this process.
- If one of your fields happens to have timestamped data, don’t bet on it copying in data as you expect.
- If SQL Server determined that the field size is smaller than some (yes, this can be as little as 1) rows actually have it, then the import process will fail. For example, if it determines that the field size should be 255 and there is one row with a column length of 256 – then this process will fail.
In other words, if it works – great. If not – you may have to drop back to one of the other methods to get the data into SQL Server or you can work with the SSIS packages that were created to facilitate this process.
And with that, we now have another way to get data into SQL Server. Next time, we’ll go into another method of getting data into SQL Server. Until then!