SQL Server 2005 introduces an enhancement to the OPENROWSET function, the new BULK rowset OLE DB provider. Via OPENROWSET, it lets you access data in files in a relational fashion. In the past, you used BULK INSERT to pull data from the file into a temp table and then ran your relational operations, but with SQL Server 2005, you can use the data contained in the file as part of your SQL statement rowset results. Keep in mind that you must specify a format file, which is the same format file you use with bcp.exe or the BULK INSERT statement. The following code shows how to access a file named c:\bulk.txt using the format file c:\bulk.fmt:
SELECT customerid, customername, totalorders
FROM OPENROWSET(BULK 'c:\bulk.txt',
FORMATFILE = 'c:\bulk.fmt') AS (customerid, customername, totalorders)
Page rendered at Sunday, January 19, 2020 10:13:37 PM (Eastern Standard Time, UTC-05:00)
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.