Bulk Insert command Explaination for loading data from text or csv file in to Sql Server


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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: