Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: Import

The BULK INSERT command is used widely for importing data into SQL Server tables. It is very handy when there is a requirement to import large amounts of data from a CSV or Text file. An example follows:-

BULK INSERT MyTable
FROM 'e:\mytextfile.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

However, there are certain scenarios when I do not want to import vast amounts of data into a table, but instead need to be able query the file directly. This can be achieved using OPENROWSET.

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]')

It is then possible to join data from the file to tables within your database:-

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=e:\MyExcelFile.xls',
                'SELECT * FROM [Sheet1$]') x
LEFT JOIN CustomerTable c
	on c.customerid = x.custid

Tags: , , , , , ,

%d bloggers like this: