Sql Server - Bulk Insert

[jehanzeb_bs]

Hello,

I want to insert data using BULK INSERT command/BCP utility in Sql Server 2000/2005.

Table structure is as follows

CREATE TABLE STUDENT
(
    STUDENTID         INT IDENTITY(1, 1), -- PRIMARY KEY,
    [NAME]            NVARCHAR(50),
    AGE               INT,
    ENROLLMENTDATE    DATETIME,
    STIPEND           FLOAT,
    IsAvtive          BIT
)

The statement used to bulk insert data is

BULK INSERT STUDENT FROM 'd:\Jehanzeb\TestFile.txt' WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = ',', TABLOCK)

The data in TestFile.txt is

-1, Jehanzeb Musani, 27, 1979-12-28T23:45:12.350, 250.25, null
-1, Rizwan Ahmed, 26, 1980-09-08T23:45:12.150, 250.76, null

The error messges encountered are

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 6 (IsAvtive).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 6 (IsAvtive).

If the value of last column (IsActive) is anythig except null, the data is inserted into the table successfully bu BULK INSERT command.

Can anyone please tell me how can I insert null in columns of type BIT using BULK INSERT command?

Thanking in advance.

Regards,
Jehanzeb

[hspc]

If the value is null, you just need to leave it blank. So remove the null from the end of the records.
So your data should be :
-1, Jehanzeb Musani, 27, 1979-12-28T23:45:12.350, 250.25,
-1, Rizwan Ahmed, 26, 1980-09-08T23:45:12.150, 250.76,

[jehanzeb_bs]

Hello,

Thanks for your prompt reply. It's working now.

Can you tell me why the same file can't be loaded via BCP utility? I am encountering the following error when I tried to bulk insert the file via [B]BCP utility[B]. It says datetime format is not valid, however, the same file is bulk inserted successfully via [B]BULK INSERT[B] command. The datetime format I am using is [B]YYYY-MM-ddTHH:mm:ss.fff[B]

Starting copy...
SQLState = 22007, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid time format

I need to ask one more thing. I want to insert data using bulk insert in parent child tables, let say, in Order and OrderDetails. does BulkInsert support this or do I have to make sure manually that first I insert tha parent file and then the corresponding file. What I want to achieve is to intermingle the parent and child records in the same file.

Thanking in advance.

Regards,
Jehanzeb

[dglienna]

It might not like the time date field.

27, 1979-12-28T23:45:12.350

[hspc]

I need to ask one more thing. I want to insert data using bulk insert in parent child tables, let say, in Order and OrderDetails. does BulkInsert support this or do I have to make sure manually that first I insert tha parent file and then the corresponding file. What I want to achieve is to intermingle the parent and child records in the same file.

Not sure
But you can try FIRSTROW,LASTROW attributes and append the detail tabe to the master table file.
But I think it does not worth it, use 2 files instead.

[jehanzeb_bs]

Hello All,

Thanks for all your support.

I can now uploading the data from BulkInsert successfully. But there is a small problem. The file I am trying to bulk insert contains six rows. Only one of these is corrupted, that is, that row can not inserted into the database because of database constraints.

I have written a component that periodically searches the file in the specified folder and upload them to database via bulk insert. If a file is successfully uploaded, it will be deleted from the folder. However, if an exception occurs while uploading the file, then the file will not be deleted (ADO.NET and Command object is used to execute BULK INSERT). The component uses a storedprocedure whose parameters are

1. TableName (Table in which data should be uploaded)
2. FileName (File that contains the rows to be uploaded)

If the file has more than ten rows that can not be inserted into the databse because of the contraints, then all the rows that have been inserted are rolled back. However, if the file contains less than ten rows that cause error while inserting them, the rows that have been inserted will not be reverted. This is I believe the default behavior of Sql Server.

What I want to do is that if the file contains even a single row that can not be uploaded succcessfully into the database, then all the rows that been inserted should be reverted back.

I am using the following sql statement in the stored procedure to upload the data

BULK INSERT TestTable FROM '\\SharedFolder\TestFile.txt'
WITH (DATAFILETYPE='char', FIELDTERMINATOR=',', TABLOCK)

Is there any way I can specify in the aforementioned statement that if even a single row can not be inserted then all the rows in the file should be reverted? I am using MAXERRORS=1 in BULK INSERT statement but it is not working.

Thanks in advance.

Regards,
Jehanzeb

[kmmreddy]

Try Using Maxerrors=0

[PeejAvery]

Welcome to the forums kmmreddy. Please remember to make your posts more relevant. This thread is over a year old.