Import Data into SQL Server Table

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