CHECKING YOUR CONSTRAINTS BEFORE OR AFTER BCP IMPORT v7.0
Valid data is essential to any good database application. SQL Server
provides many mechanisms for ensuring validity, including CHECK
constraints. Though Microsoft provides these tools for maintaining the
validity of data, there are always ways to "get around" them.
BCP.EXE, for example, can load data with or without validating against
CHECK constraints defined on a particular table. You can perform this
CHECK on import or after import is completed. Assume the following
table structure and scenario:
CREATE TABLE TableX (FName VARCHAR(10) NOT NULL,
LName VARCHAR(10) NOT NULL,
Sex CHAR(1) NULL)
ALTER TABLE TableX ADD CONSTRAINT MF_CK1 CHECK (Sex = 'M' or Sex = 'F')
This creates a table and defines a CHECK against the Sex field to allow
for only "M" (male) or "F" (female).
Flat file data.
John Smith M
Jane Doe F
Steve Martin B
Using BCP to import this flat file will not result in a CHECK
constraint violation unless you use a BCP hint, i.e.,
C:\>BCP pubs.dbo.Tablex in d:\temp\x.dat -c -Usa -Ppassword -S -
h"CHECK_constraints"
This method will tell BCP to validate the data against the table CHECK
constraints, and BCP will fail on the "B" entry.
Alternatively, you can let BCP import the data without the CHECK
constraint hint, but now you have invalid data in your table. You can
verify its validity by issuing a simple update statement against the
table that directs each field to update to itself. This will catch any
data validation issued against the CHECK constraints of the table.
C:\>BCP pubs.dbo.Tablex in d:\temp\x.dat -c -Usa -Ppassword -S
update TableX set FName=Fname, LName = LName, Sex = Sex
This will fire the CHECK constraint, and you will receive an error,
alerting you to invalid data.
Home Previous Next