Inserting Bulk Data in SQL Server from Text file

Introduction

This article is simple one for inserting bulk data from text file in to sql server by using a simple query. 

Prerequisite 

SQL Server 2005

Description

Actually in my blog, one guy asked me how to insert bulk data’s in to sql server 2005.  When I tried to answer him I just wrote this article. There is a simple query to do this.

Here with I have given the query for creating a sample table and query for inserting bulk data from text file. 

Query

The query for creating table,

CREATE TABLE Employee
(
 FName varchar (100) NOT NULL,
 LName varchar (100) NOT NULL,
 Email varchar (100) NOT NULL
)


//Then we have a text file with the bulk datas like as follows,

 vinoth,kumar,itvinoth83@gmail.com
emp1FName,emp1LName,emp1@company.com
emp2FName,emp2LName,emp2@company.com
emp3FName,emp3LName,emp3@company.com
emp4FName,emp4LName,emp4@company.com
emp5FName,emp5LName,emp5@company.com
emp6FName,emp6LName,emp6@company.com
emp7FName,emp7LName,emp7@company.com
emp8FName,emp8LName,emp8@company.com
emp9FName,emp9LName,emp9@company.com
emp10FName,emp10LName,emp10@company.com
emp11FName,emp11LName,emp11@company.com
emp12FName,emp12LName,emp12@company.com

//Now the query for inserting bulk data is, 

BULK INSERT Employee FROM 'c:\bulktext.txt' WITH (FIELDTERMINATOR = ',')

//Now you can see the data's inserted in table by the select query as follows,

select * from Employee 

Conclusion

Hence we are inserting the bulk data’s in to sql server by using a simple query. The thing is that we need to keep a formatted text file with appropriate data’s to insert.