T-SQL Script to import data from Excel to SQL Server Table

Let’s see configuration steps and single T-SQL code to import data from Excel to SQL Server Table.

Assume we have below mentioned excel data which is named as Import_Excel.xlsx. It contains 2 fields as ID and Name. And sheet name is [SQL Import].

ID	Name
101	Joseph
102	Jack
103	Raja
104	Antony

We will create a table in SQL Server with the same structure as in Excel.

CREATE TABLE Test_Import

                                    (

                                                ID INT,

                                                Name VARCHAR(100)

                                    )

Configuration Steps

1.  Enable OPENROWSET/OPENDATASOURCE in SQL Server.

            SP_CONFIGURE 'show advanced options', 1

RECONFIGURE;

            EXEC SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

            RECONFIGURE;

2. Make sure whether Excel 2010 ACE driver has been installed on your system. If not, install it from below mentioned Microsoft link.

http://www.microsoft.com/en-us/download/details.aspx?id=13255

After installation, restart the system to make it affected.

3.   Make sure that login which we are using having access rights on SQL Service or not. If not, follow below steps to provide access rights on SQL Service for your login.

a)   Start --> Run --> Type “services.msc”.

b)   Go to SQL Server (MSSQLServer) Service --> Right click à Properties.

c)    Go to Log on Tab --> use This Account to configure your login access.

d)   Restart the SQL Service.

4.   Run below mentioned script.

       USE [master]

       GO

       EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

        GO                  

       EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

       GO

5.  Excel has to be closed while running below T-SQL script to import data from Excel to SQL Server table.

               INSERT INTO [EmployeeDB].[dbo].[Test_Import]

               SELECT *

               FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\Import_Excel.xlsx','SELECT * FROM [SQL Import$]');

      

          [HDR] indicates whether you have header in your excel or not.

          [C:\Import_Excel.xlsx] is the excel source name and location

          [SQL Import$] is the sheet name.