Solved

bcp is not following the check constraint

Posted on 1998-08-25
2
606 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
ID: 1089745
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
ID: 1089746
Thank you.  That's exactly the information I needed.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
export sql results to csv 6 36
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
Get the latest status 8 32
[SQL server / powershell] bulk delete table from CSV 8 33
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

778 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