Import Data into SQL Server Table

This post will help you learn how to import data into SQL Server table from .CSV, .XML, .XLS and .XLSX file.

I created a sample table "Employee1" for testing the import queries:

Employee1 table has 2 columns:
ID (varchar(5))
Name (varchar(50))

Example of importing data from an .XML file:

INSERT INTO Employee1 (Id,Name)
SELECT 
c3.value('Id[1]','INT') AS Id,
c3.value('Name[1]','VARCHAR(10)') AS Name
--INTO Employees –-(used when there is no existing table)
FROM 
(
 SELECT CAST(c1 AS XML)
 FROM OPENROWSET 
 (
  BULK 'C:\a.XML',SINGLE_BLOB
 ) AS T1(c1)
)AS T2(c2) 
CROSS APPLY c2.nodes('/Employees/Employee') T3(c3)

Example of importing data from .csv file:

BULK INSERT Employee1 FROM 'C:\Import.csv'
WITH
(
FIELDTERMINATOR = ',', --delimiter used to saparate values
FIRSTROW=2,
ROWTERMINATOR = '\n'   --character terminates parsed row
)

Example of importing data from .xls file:
[.xls file had 2 columns - Id & Name]

INSERT INTO Employee1
SELECT *
--INTO Employees –-(used when there is no existing table)
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\a.xls;',
                'SELECT * FROM [Sheet1$];')

.xls file should be closed. When it is open and you run the above query, it shows the below error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


If you encounter below error:

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.,

run below given commands:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE WITH OVERRIDE
GO

Example of importing data from .xlsx file:
[.xlsx file had 2 columns - Id & Name]

INSERT INTO Employee1                 
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=c:\a.xlsx',
    'SELECT * FROM [Sheet1$]');

If you receive any error, then try downloading and installing AccessDatabaseEngine and run:

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