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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook