Post reply

Bulk insert problems

  • July 4, 2011 at 3:21 pm

    #240231

    Attempting to do a bulk insert into table below

    TABLE CITIES

    [ccode] [char](2) NULL,

    [city] [varchar](100) NULL,

    [rcode] [char](2) NULL,

    [iguid] [uniqueidentifier] NULL,

    [uguid] [uniqueidentifier] NULL,

    [rguid] [uniqueidentifier] NULL

    With the following command

    bulk insert cities

    from 'c:\worldcities1a.txt'

    with

    (

    fieldterminator = ',',

    rowterminator = ''

    )

    worldcities1a.txt data example

    'ad','Aixàs','06'

    'ad','Aixirivali','06'

    'ad','Aixirivall','06'

    'ad','Aixirvall','06'

    'ad','Aixovall','06'

    'ad','Andorra','07'

    'ad','Andorra la Vella','07'

    'ad','Andorra-Vieille','07'

    'ad','Andorre','07'

    'ad','Andorre-la-Vieille','07'

    'ad','Andorre-Vieille','07'

    'ad','Ansalonga','04'

    'ad','Anyós','05'

    Getting the following errors if either "rowterminator = ''" or "rowterminator = '\r'"

    Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 1, column 1 (ccode).

    Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 2, column 1 (ccode).

    Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 3, column 1 (ccode).

    Msg 4863, Level 16, State 1, Line 1

    Bulk load data conversion error (truncation) for row 4, column 1 (ccode).

    Msg 4863, Level 16, State 1, Line 1

    Any help?

  • July 4, 2011 at 6:29 pm

    #1348031

    There is a syntax error with your CREATE TABLE Statement.

    Also you have a syntax error on your Terminators of your Bulk Insert.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 4, 2011 at 6:32 pm

    #1348032

    Thanks

  • July 4, 2011 at 6:38 pm

    #1348033

    I would recommend using a pipe "|" for your row terminator. If you have a comma in your text it messes up the load.

    You row terminator is not being recognized.

    HTH.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 4, 2011 at 7:12 pm

    #1348036

    CREATE TABLE CITIES (

    ccode varchar(200) NULL,

    [city] [varchar](100) NULL,

    [rcode] varchar(200) NULL,

    [iguid] [uniqueidentifier] NULL,

    [uguid] [uniqueidentifier] NULL,

    [rguid] [uniqueidentifier] NULL

    )

    Conternts of TxtFile3.txt

    ad,Aixàs,06,,,

    ad,Aixirivali,06,,,

    ad,Aixirivall,06,,,

    ad,Aixirvall,06,,,

    ad,Aixovall,06,,,

    ad,Andorra,07,,,

    ad,Andorra la Vella,07,,,

    ad,Andorra-Vieille,07,,,

    ad,Andorre,07,,,

    ad,Andorre-la-Vieille,07,,,

    ad,Andorre-Vieille,07,,,

    ad,Ansalonga,04,,,

    ad,Anyós,05,,,

    bulk insert cities

    from 'C:\SSIS\TxtFile3.txt'

    with

    (

    fieldterminator = ',',

    ROWTERMINATOR = ''

    )

    (13 row(s) affected)

    SELECT * FROM CITIES

    adAixas06NULLNULLNULL

    adAixirivali06NULLNULLNULL

    adAixirivall06NULLNULLNULL

    adAixirvall06NULLNULLNULL

    adAixovall06NULLNULLNULL

    adAndorra07NULLNULLNULL

    adAndorra la Vella07NULLNULLNULL

    adAndorra-Vieille07NULLNULLNULL

    adAndorre07NULLNULLNULL

    adAndorre-la-Vieille07NULLNULLNULL

    adAndorre-Vieille07NULLNULLNULL

    adAnsalonga04NULLNULLNULL

    adAny=s05NULLNULLNULL

    TRUNCATE TABLE CITIES

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 4, 2011 at 8:07 pm

    #1348040

    Hell I better add more.

    I just used part of the create statement to show the table structure - it was not the actual create statement.

    The bulk insert adds in the quotes as well as the data to the fields.

    I changed the char fields to a longer varchar field to see what was happening.

    I needed to use quotes as some of the city values included quotes.

    The actual row terminator got eaten when I posted.

    The bulk insert does not honor sdf conventions.

    As I was inserting 2.5 million records about the only method was a bulk insert.

    I just added the quotes and all and modified the field values using sql then modified the fields after.

    I managed to get all the records loaded

    thanks

    rykk

  • July 4, 2011 at 8:26 pm

    #1348045

    Good deal.

    Try using a delimiter another than ",".

    You can use "|" but I had a situation where someone placed a Pipe in the text which caused the job to fail.

    So I used a non displayable character sequence. It was so long ago that I can't recall what I used. 🙂

    Good luck.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 4, 2011 at 8:58 pm

    #1348046

    I actually used a pipe on your suggestion even thought I knew there were no commas in the city names

  • July 5, 2011 at 7:13 am

    #1348256

    Are you going to post your solution?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • July 5, 2011 at 2:50 pm

    #1348596

    sales 77914 (7/4/2011)

    I actually used a pipe on your suggestion even thought I knew there were no commas in the city names

    Yes but you will often encounter commas in text fields.

    Feel free to post you final solution.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 10 posts - 1 through 10 (of 10 total)

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