Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

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
Advertisements

Tags: , , , , , ,

%d bloggers like this: