Post reply

BULK INSERT Problem

  • March 17, 2004 at 3:25 pm

    #107793

    Help!

    I am trying to BULK INSERT a.dat into table x in my database.  I am using

    DELETE x

    BULK INSERT x FROM a.dat

    Table x has one field y nvarchar(250)

    Here is my problem.  a.dat has all kinds of data in it; null, binary data, etc.

    If the records in a.dat where they are in order by A, B, C, D, E

    I would expect them to be in the same order after the BULK INSERT.

    BUT Table x could end up C, D, E, B, A.  I get the same number of records, but they are not in a.dat order.

    I am assuming it is because of the data in a.dat, but I can't do anything about it UNTIL I get it into table x.

    Should I be using another datatype than nvarchar?

    I need them in exactly table x to be in exactly the same order as a.dat.

    Thanks,

    Phil Krieg

     

     

     

     

  • March 17, 2004 at 4:55 pm

    #499236

    Try adding an "ID" column with an identity to your table.

    CREATE TABLE x

    (ID INT IDENTITY(1,1)

    , YourData varchar(255)

    )

    You should then be able to do your order by on the ID column. What is the reasoning behind needing the order specific?




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • March 17, 2004 at 9:33 pm

    #499245

    Gary,

    Sounds like this would work.  But I don't know how to create a FORMATFILE that would skip column 1 and insert the data into column 2 for each row.  Can you help me or guide me to where I could find out more about it?

    The reason I need the records in the original order is because they are pre-sorted in the a.dat file and passed to me as they want them to be used.

    Thanks, 

    Phil

     

     

  • March 18, 2004 at 1:15 pm

    #499405

    The easiest way to create a format file is to use BCP. You can do this by the following command.

    bcp dbname.dbo.tablename format -SMachineName -T -fFormatFileName.fmt

    You can just hit the enter key for the defaults.

    Note that in BOL "Using Format Files" it states "Note  It is possible to skip importing a table column if the field does not exist in the data file by specifying 0 prefix length, 0 length, 0 server column order, and no terminator. This effectively states that the data field does not exist in the data file, and that the server column should not have data loaded into it." so you should be able to modify the format file appropriately.

    Good luck!




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply