Getting Started with ASP.NET MVC - Part 5: How to do programming with razor syntax
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 113
sqlserver 94
BRH 78
#SQLServer 65
#TSQL 55
SQL Server 32
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2011 7
November 2007 7
November 2011 6
August 2011 6
October 2011 6
July 2011 6
September 2011 6
December 2011 6

Madhivanan's TSQL Blog

Best practices when using BULK INSERT

Dec 22 2010 3:18AM by Madhivanan   

In the SQL Server forums, I see questions complaining that BULK INSERT is not taking care of check constraints. There are also some other questions related to BULK INSERT. Here I summarise them and explain

1 Enabling Check contraint - Use the option check_constraints

create table test
(id int,
amount int check(amount >=1000 and amount<=5000));

Consider the following data in the text file

1	700
2	2000
3	870
4	4500

The following query will not check the constraint

bulk insert test
from 'f:\test.txt'
with
(fieldterminator=',',
rowterminator='\n')

To enable the check constraint specify the option check_constraints

bulk insert test 
from 'f:\test.txt' 
with 
(fieldterminator=',', 
rowterminator='\n', 
check_constraints) 
select * from test 

2 Limiting the rows - Use the options FIRSTROW and LASTROW.

This copies the first three rows

bulk insert test
from 'f:\test.txt'
with
(fieldterminator=',',
rowterminator='\n',
FIRSTROW =1,
LASTROW=3)

3 Error handling - Use the option ERRORFILE

The row terminator is not correct. So an error file is generated

bulk insert test
from 'f:\test.txt'
with
(fieldterminator=',',
rowterminator='\',
FIRSTROW =1,
LASTROW=3,
ERRORFILE ='F:\error.txt',
check_constraints)

Also refer these posts about BULK ISERT
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx http://beyondrelational.com/blogs/madhivanan/archive/2010/12/15/bulk-insert-comma-as-part-of-data.aspx

Tags: t-sql, sql_server, bulk_insert, sqlserver, tsql, BRH, #TSQL, #SQLServer,


Madhivanan
4 · 39% · 8773
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • Hi,

    Thanks for this.

    -Chintak

    commented on Dec 22 2010 6:09PM  .  Report Abuse This post is not formatted correctly
    Chintak Chhapia
    58 · 4% · 860

Your Comment


Sign Up or Login to post a comment.

"Best practices when using BULK INSERT" rated 5 out of 5 by 1 readers
Best practices when using BULK INSERT , 5.0 out of 5 based on 1 ratings
    Copyright © Beyondrelational.com Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising