My Profile
Gifts
Active Members
TodayLast 7 Days
more...
|
Bulk Operations in SQL Server
Posted Date: 31 Jul 2008 Resource Type: Articles Category: Databases
|
Posted By: Vijaya Member Level: Diamond
Rating:  Points: 15
|
Overview:
In this article I would like to give you an overview on bulk operations in SQL Server. This article is mainly for beginners. This will talk about, some of the very basic things in Bulk operations. For more information either you can visit MSDN or refer BOL.
Introduction:
There are some common tasks which we do in our day-to-day life like
"Transfer the data from .csv file to SQL Server" or "export the table data from SQL Server to .txt file".
You can transfer the data from .csv file to SQL Server using DTS (in SQL Server 200) or SSIS (in SQL Server 2005) Or you can even develop some small .Net utility to do this job. Well instead of going with these options, you can use "BULK INSERT" feature in SQL Server.
We will discuss following items as part of this article:
1) Bulk Insert
This is mainly to transfer the data from out side world to SQL Server.
2) Bulk Copy
This is mainly to transfer the data from SQL Server to outside world.
What are we waiting for, let’s get into the details.
Bulk Insert
The bulk insert command can be used within T-SQL script or in a stored procedure to import data into SQL Server. Before we get into the detailed discussion let’s look at the syntax first.
Let’s create EMPLOYEE table with 3 columns.
CREATE TABLE EMPLOYEE
(EMPID INT,
ENAME VARCHAR(10),
SALARY INT);
Create employee.csv file with the following information
empid,ename,salary
1234,steve,100
2345,ross,200
3456,bill,300
Use the below command to do Bulk Insert
BULK
INSERT Employee
FROM 'c:\Vijaya\Dotnetspider\employee.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2
)
The first thing that you need to understand about bulk insert is that every column from the source file is simply inserted directly into the destination table using a one-to-one mapping. The first column from the source file is dumped into the first column of the destination table. If there are too many columns in the destination table, it will fail.
Field Terminator: used to delimit columns in the source file. The default one is a comma. There are some cases where i have seen "|" also. "," can be part of Name. So in that case the bulk insert may fail.
Row Terminator: This is basically to specify the delimiter between the rows. '\n' means end of row and is the typical setting.
FirstRow: this is useful when specifying whether the incoming file has column headers or not. If the file does have column headers, use this option to indicate that the first row of data is actually the second row of the file.
There are other options that are available with this.
TabLock: This option places an exclusive lock on the entire table. If you want to protect your data load from other users then its worth to lock the table and do the data load and release this to other users to consume/select the data. This option can dramatically improve performance.
Rows per Batch: this option tells SQL Server to insert n number of rows in a single batch, rather than the entire file. This option can dramatically improve performance
Max Errors: This option tells how many rows can fail before the bulk inserts fails.
Errorfile: option points to a file that will collect any rows not accepted by the bulk insert operation.
Bulk Copy (BCP)
BCP, short for bulk copy program is a command-line tool/utility of bulk operations. It is slightly different from bulk insert. In bulk insert, only one way operate i.e. inserting data into SQL Server but BCP is command-line tool and can import or export data.
Let’s look at below example:
C:\> bcp pubs.dbo.Employee out c:\Vijaya\Dotnetspider\employee.txt -n -Usa -P -eC:\Vijaya\Dotnetspider\error.txt
Employee is the table for which we want to export the data into a file called employee.txt with the user “SA”. If there are any errors in this process log that information into error.txt file.
Summary
Happy learning
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|