Populate a table from a .CSV or .TXT file using SQL Server 2005

You can use Bulk Insert or SSIS to import text/csv files. There are some advantages and disadvantages using any of these methods. In this article, we will explore how to use the OPENROWSET to read a data file and populate a table.

Note: In SQL Server 2005, OPENROWSET can read from a data file without loading the data into a target table. This lets you use OPENROWSET with a simple SELECT statement.

Follow these steps:

Step 1: Create a database called 'Employees'. Create a table called '
EmployeeDetails' in it using the script given below:

USE [Employee]
GO
/****** Object: Table [dbo].[EmployeeDetails] Script Date: 04/11/2008 11:12:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[EmployeeDetails](
[EmployeeID] [nvarchar](50) NOT NULL,
[EmployeeName] [varchar](50) NULL,
[EmployeeAddress] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Step 2: To run ad-hoc queries on the SQL server, you would first need to enable it using the following query:

sp_configure 'show advanced options',1
RECONFIGURE WITH override
GO
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE WITH override
GO

Step 3: Create a txt/csv file with the following format on your C:\. The file is called 'Employee.csv'

EmployeeID EmployeeName EmployeeAddress
1 Kat 23/Avenue. Park
2 Jim Jeoff Street
3 Tom Lifer Road

Step 4: The final step is to run the query and populate the EmployeeDetails table

USE Employee
GO
INSERT INTO EmployeeDetails(EmployeeID,EmployeeName,EmployeeAddress)
SELECT *
FROM
OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=C:\;Extensions=CSV;','SELECT * FROM Employee.csv')

References : http://msdn2.microsoft.com/en-us/library/ms190312.aspx