One of the very popular questions I receive is how to import data from Excel in SQL Server. Let us learn about that in this blog post and we will use SQL Server Management Studio for this exercise.
First, create any sample data in Excel. I will be using this sample excel for our example. Once you have your sample ready, you just have to follow the images displayed here to import the data.
Well, there you go. Importing data into SQL Server is very easy if you have selected the right options. Not only excel but you can also import data from flat files, CSV and many other data sources. You can also generate an automatic SSIS package from the SSMS and schedule it to run automatically.
Here are a few additional blog posts which you may find interesting:
- SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server
- SQL SERVER – Import CSV File into Database Table Using SSIS
- SQL SERVER – Easy Way to Import and Export SQL Server Data
- SQL SERVER 2016 – How to Import New Sample Database WideWorldImporters
- SQL SERVER – Enable Identity Insert – Import Expert Wizard
- SQL SERVER – Importing CSV File Into Database – SQL in Sixty Seconds #018 – Video
- SQL SERVER – Powershell – Importing CSV File Into Database – Video
- SQL SERVER – FIX – Export Error – ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine
Importing data into a database is one of the most important tasks. I often receive questions regarding what is the quickest way to insert CSV data or how to import CSV Data into SQL Server Table. Honestly, the process is very simple and the script is even simpler. In today’s SQL in Sixty Seconds Video we will learn how quickly we can insert CSV data into SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Excellent. Please show us the method of transferring data from SQL Databases to excel sheets ,so that we can save the files in local machines.
Use Excel’s PowerQuery. Google it, there’s a lot of tutorials already.
The instructions are right assuming all the software is in the same architecture (Excel and SSMS both are 32 or 64 bit).
If Excel is 64 (which is now default for many) and SSMS (or rather the “import export wizard” part of it) is 32 (you cannot download and install the mentioned IEW in 64 bit, it’s only available when you set up the SQL server as part of the package) then you will be greeted with a message saying wizard cannot read the Excel file. You have to save the Excel file as 97-2000 compatibility first.