Solved

bcp is not following the check constraint

Posted on 1998-08-25
2
597 Views
Last Modified: 2006-11-17
Hi,

I have a check constraint on several columns of integers.  When I use bcp, even when the values being imported are outside the specified domain, bcp imports them in just fine.
When I use an insert statement manually, the constraints are enforced, so I don't think it's my syntax.

How can I get bcp to follow the check constraints and not go outside my desired domain?

A part of my create table statement:
num_5000   INTEGER   NOT NULL
    CHECK (num_5000 BETWEEN 0 and 708),

The bcp command for a tab delimited ASCII text file:
bcp chiptray..core_data in temp.txt /SSERVER /Usa /Pxxx /c

And finally, bcp is called from within a batch file.

Thanks.  :-)
0
Comment
Question by:atclark
2 Comments
 
LVL 3

Accepted Solution

by:
gmoriak earned 50 total points
Comment Utility
I'm sorry, but BCP can't be made to check constraints.

BCP is so quick because it ignores things like constraints and triggers.  You have three options as I've learned.

1.  BCP into an alternate table and create a process to transfer items to your real table.  This process (sp, insert/select,  etc.) will use the constraints.
2.  Create a process to check and report constraint violations after BCP.
3.  Say oh well and live with constraint violations or change the process that creates the input file to enforce constraints so there are no problems
0
 

Author Comment

by:atclark
Comment Utility
Thank you.  That's exactly the information I needed.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now