Sql Server - Bulk Insert
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8

Thread: Sql Server - Bulk Insert

  1. #1
    Join Date
    Jul 2002
    Location
    Karachi, Pakistan
    Posts
    59

    Sql Server - Bulk Insert

    Hello,

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

    Table structure is as follows

    Code:
    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

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

    Code:
    -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

    Code:
    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

  2. #2
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,209

    Re: Sql Server - Bulk Insert

    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,
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

  3. #3
    Join Date
    Jul 2002
    Location
    Karachi, Pakistan
    Posts
    59

    Re: Sql Server - Bulk Insert

    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]

    Code:
    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

  4. #4
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,982

    Re: Sql Server - Bulk Insert

    It might not like the time date field.
    27, 1979-12-28T23:45:12.350
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  5. #5
    Join Date
    Apr 2002
    Location
    Egypt
    Posts
    2,209

    Re: Sql Server - Bulk Insert

    Quote Originally Posted by jehanzeb_bs
    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.
    Hesham A. Amin
    My blog , Articles


    <a rel=https://twitter.com/HeshamAmin" border="0" /> @HeshamAmin

  6. #6
    Join Date
    Jul 2002
    Location
    Karachi, Pakistan
    Posts
    59

    Re: Sql Server - Bulk Insert

    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

    Code:
    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

  7. #7
    Join Date
    Apr 2008
    Posts
    1

    Re: Sql Server - Bulk Insert

    Try Using Maxerrors=0

  8. #8
    PeejAvery's Avatar
    PeejAvery is offline Super Moderator Power Poster
    Join Date
    May 2002
    Posts
    10,869

    Re: Sql Server - Bulk Insert

    Welcome to the forums kmmreddy. Please remember to make your posts more relevant. This thread is over a year old.
    If the post was helpful...Rate it! Remember to use [code] or [php] tags.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center