Bulk inserting into table with computed columns

tperovic

Using SS2K, I'm getting the following error while bulk inserting:

Column 'warranty_expiration_date' cannot be modified because it is a
computed column.

Here is my bulk insert statement:

BULK INSERT dbo.TestData
FROM 'TestData.dat'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='TestData.fmt')

The computed column is not referenced in the format file and the data file
does not contain the computed data.

Thanks

Jul 20 '05 #1

Erland Sommarskog

tperovic (tp******@compumation.com) writes:

Using SS2K, I'm getting the following error while bulk inserting:

Column 'warranty_expiration_date' cannot be modified because it is a
computed column.

Here is my bulk insert statement:

BULK INSERT dbo.TestData
FROM 'TestData.dat'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='TestData.fmt')

The computed column is not referenced in the format file and the data file
does not contain the computed data.

Could you provide more information, for instance a CREATE TABLE statement,
a sample data file and a sample format file that demonstrates the problem.

To wit, I created this table:

create table c (a int NOT NULL,
b as sqrt(a))

And I created this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 1 dda_num ""

And used this data file:

12
4144
356

And this command:

bulk insert c FROM 'E:\temp\slask.bcp'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='E:\temp\slask.fmt')

And my load was successful.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

tperovic

Facing a deadline, I dropped the computed column and added it to a view.
Maybe later we can revisit this issue. Thanks.

"Erland Sommarskog" <so****@algonet.se> wrote in message
news:Xn*********************@127.0.0.1...

tperovic (tp******@compumation.com) writes:

Using SS2K, I'm getting the following error while bulk inserting:

Column 'warranty_expiration_date' cannot be modified because it is a
computed column.

Here is my bulk insert statement:

BULK INSERT dbo.TestData
FROM 'TestData.dat'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='TestData.fmt')

The computed column is not referenced in the format file and the data file does not contain the computed data.

Could you provide more information, for instance a CREATE TABLE statement,
a sample data file and a sample format file that demonstrates the problem.

To wit, I created this table:

create table c (a int NOT NULL,
b as sqrt(a))

And I created this format file:

8.0
1
1 SQLCHAR 0 0 "\r\n" 1 dda_num ""

And used this data file:

12
4144
356

And this command:

bulk insert c FROM 'E:\temp\slask.bcp'
WITH (CHECK_CONSTRAINTS,
FIELDTERMINATOR='|',
MAXERRORS = 1,
FORMATFILE='E:\temp\slask.fmt')

And my load was successful.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3