Sunday, 6 May 2012

SQL Server- Import rows from CSV/txt files to an existing SQL Server table using Bulk Insert


In this post I am explaining about Bulk Insert utility in SQL Server, Using bulk insert we can import CSV (comma separated value) or txt file data to an existing table of SQL Server.

For Example:-

create table student
(
row_id int ,
fname varchar(15),
lname varchar(15)
);


Create a CSV file or a txt file with the following content and save it to "C:\SQL_Server\test.txt" location :-

1, Adoms ,Mcknight
2,Joe,Smith
3,Joe,Doe
4,Alex,Smith

Open a notepad and copy this statement and save as a batch (.bat) file

sqlcmd -U sa -P root -S "myhome-pc\my" -d test -q " BULK INSERT students FROM 'c:\SQL_Server\test.txt' WITH (FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')  select * from students"   -o  C:\SQL_Server\output.txt 

 Where
          -U  = User name
          -P   = password
          -d   = Database name
          -q  = Query


 You can see help for sqlcmd-
Sqlcdm -?

test.txt is a file name from where I want to insert rows in table students.
Output.txt file name which will automatically generate when we run .bat file in C:/SQL_Server directory. 
                Select * from students _results is the output query whose result will show on output.txt file.
We can run directly .bat file by double click or from command prompt.


No comments: