Thursday, May 2

BULK INSERT-SQL


Bulk Insert

Imports a data file into a database table or view in a user-specified format. This usually helps when you have so many text files/Data files to migrate to database.

For Eg.
Consider, I have Text files such as Text_1,Text_2,Text_3. With data such as shown below.







Note:As field terminator you can use ' , ' or ' | '

Simple Syntax:

BULK INSERT 
[ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ]
FROM 'data_file(Path of the file)' WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')

Bulk Insert statement for Text_1.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_1.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Bulk Insert statement for Text_2.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_2.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Bulk Insert statement for Text_3.
BULK INSERT JOHN_TEST.dbo.Namelist FROM 'D:\jjn\Namelist\Text_3.TXT'
WITH(FIELDTERMINATOR ='|',ROWTERMINATOR = '\n')


Before executing this statements I should create a table called Namelist in my JOHN_TEST database

Create Table Namelist (ID_NO int, Name varchar(255), City varchar(255))

Now these Bulk Insert statements can be executed; keep in mind that your text files should be in same server that you are working on. i.e. if you are working in local sys. then place in local itself or if working in servers;place text files in servers, otherwise it will throw out error.

Output:

This is a way to migrate data from text files/Flat files at time to DB. This query can be used in stored procedures when needed.