Saturday, 5 July 2014

Reading text file in SQL Server

There are many option to read text file data and store it into database. We may have following options available for reading/storing/updating data into database
1.  Import and Export wizard of SQL Serve
2. SQL Server Integration Services
3. Using C# with SQL CLR will insert data into tables
But , using few lines code of query need to execute in SSMS.
SQL Server provides "BULK INSERT" Operation to read data from local system and store it into database tables.
Steps :
1. First create text file with some name "Abc.txt" and enter values as
nihar,
Vishal,
Vivek,
Save and Copy path of file.
2. Open SSMS and create temp table for data insertion
Create table temp_Data
(
name nvarchar(20)
)
3.  Use following query to view operation
BULKINSERT Temp_dataFROM  'D:\Abc.txt'WITH(
FIELDTERMINATOR 
=',',     -- Here you can use any delimiter for seperation
ROWTERMINATOR 
='\n'  );
SELECT * FROM Temp_data
o/P :  3 Rows Affected
Here some arguments commonly used in BULK INSERT
-  FIELDTERMINATOR
-  ROWTERMINATOR



No comments:

Post a Comment