Importing Data from CSV

Below are sample T-SQL Script to demonstrate data import from .csv flat file using OPENROWSET applying 2 different providers and applying BULK INSERT.  

USE AdventureWorks2008;

 

EXEC sp_configure

GO

-- [Ad Hoc Distributed Queries] run_value should be 1

 

SELECT * FROM OPENROWSET('MSDASQL',

'Driver={Microsoft Text Driver (*.txt; *.csv)};

DefaultDir=F:\data\export\csv\;',

'SELECT * FROM Top10.csv')

GO

------------

 

--Using a different provider

SELECT *

FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Text;Database=F:\data\export\csv\;HDR=YES',

'SELECT * FROM Top10.csv')

GO

------------
 
-- T-SQL import local csv file with BULK INSERT
BULK INSERT InventoryDB.dbo.InvStage
   FROM 'C:\data\importstage\ukinventory.csv'
   WITH
       (
          FIELDTERMINATOR = ',',
          ROWTERMINATOR = '\n'
       )

------------

 Thanks to SQLUSA team.