Hi All,

To insert the data from file to sql server we are using

1. BCP utility

2. Bulk insert

3. Open rowset

Today we will discuss the Bulk Insert command

It imports a data from the file like flat file, CSV file into the database

We have text file like below

In order to load the data from the text file into sql server table, first we need to create the table

CREATE TABLE Empbulkinsert

(

empno int,

empname varchar(10),

salary money

)

And we are using the below command for loading the data from the file to sql server table

In the above file, we have seen for every field is separated by Comma (,) and every row is separated by enter

Execute the script so that, we will got

Result:

You can see the syntax of the bulk insert command

Reference http://msdn.microsoft.com/en-us/library/ms188365(v=SQL.90).aspx

If the file is containing only single column of data

You can use the bulk insert command without using with key word

If you have the column names in the file,

And we are trying to loading in to sql server, we got the below error

To fix this, we have first row argument, specify from which line number the data is coming

We can also specify how many errors you want to allow before considering that the BULK INSERT failed

From above if error is greater than >0, then bulk insert operation failed

We can use the arguments above to perform validations

We can import the data from the remote system using \ServernameSharenamePathFilename

BULK INSERT AdventureWorks2008R2.Sales.SalesOrderDetail

FROM ‘\computer2salesforcedailyordersneworders.txt’;

Tomorrow we will discuss about Open rowset provider.

Please check and let us know if you have any concerns on this.

Thanks for viewing

Regards,

Chaitanya,

Webiste :  https://mssqlbuzz.wordpress.com/

To Send Mail : mssqlbuzz@gmail.com