Post reply

Bulk Insert issue

  • June 25, 2009 at 1:27 am

    #131465

    I've been loading a delimited text file into a temp (staging) table via BULK INSERT.

    I have noticed that occasionally, the first (header) line (maybe more, definitely the first one) doesn't get loaded in.

    Running the bulk insert again (sometimes several times) will eventually get the file loaded in.

    I've even tried loading just the first row, followed by all but the first row. Even this didn't help.

    I've had problems with files ranging from about 1200 lines (600k) to > 14,000 (5+mb). There will be larger files to load in the near future. I'm not (knock on wood) having a problem with smaller files that I have to load in (~1k).

    Has anyone else noticed this? Is there any kind of a solution for this behavior?

    FYI, I'm running Vista 64-bit, sql 2008 dev

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 25, 2009 at 2:22 am

    #1015125

    can you share the command you are using and other relevant information please.

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

  • June 25, 2009 at 3:18 am

    #1015152

    hi

    can you please help to me

    how to import fixedlength file into sql server

    please help to me

    Advance thanks.

    Rajan

  • June 25, 2009 at 7:11 am

    #1015279

    hi_abhay78 (6/25/2009)

    can you share the command you are using and other relevant information please.

    CREATE TABLE #RawData (LineText varchar(max))

    BULK INSERT #RawData FROM "MyFile"

    I've tried:

    BULK INSERT #RawData FROM "MyFile" WITH (LastRow = 1)

    followed by

    BULK INSERT #RawData FROM "MyFile WITH (FirstRow = 2)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 25, 2009 at 7:12 am

    #1015280

    itslevel (6/25/2009)

    hi

    can you please help to me

    how to import fixedlength file into sql server

    please help to me

    Advance thanks.

    Rajan

    Hey Rajan, why don't you start your own thread for your problem? This thread is for the issue I'm having with the BULK INSERT command.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 25, 2009 at 3:18 pm

    #1015651

    Can u try BCP utility in SQL server

  • June 25, 2009 at 11:53 pm

    #1015765

    hi,

    I did not use SQL Server BCP utility function can you please provide the idea and sample code

    Note: Actually our data has below format so I want to import fixed length values

    010009394620090527OTEL-AB 200812145MWDSLLD 000300032009062259729 @NONE@ 000770113307289CO000790096735524OH000AB6142754330608 MUSTAFA HAYKAL 2214 EAKIN RD COLUMBUS OH4322332222214 EAKIN RD COLUMBUS OH43223322200A075D4BA805314 6142754330608885 100D5015C 000000CN0010001MB 00320060427CLMBOH27DS0COLUMBUS 27 DS0 000000000 000000000 003100054 0051 000000001001000002000001000000000000000000000000000 000001000010109D20071130 0000000000016142754330 G000010109D20071130 0012009051900120090519Y11 OHIO 1N6496

    Can you please help to me?

  • June 26, 2009 at 12:04 am

    #1015773

    Hi

    I tryed via package but i need another way to import below format of the records,

    Actully fixed length delimiter files have been our end

    so how do make it please help to me .

    010009394620090527OTEL-AB 200812145MWDSLLD 000300032009062259729 @NONE@ 000770113307289CO000790096735524OH000AB6142754330608 MUSTAFA HAYKAL 2214 EAKIN RD COLUMBUS OH4322332222214 EAKIN RD COLUMBUS OH43223322200A075D4BA805314 6142754330608885 100D5015C 000000CN0010001MB 00320060427CLMBOH27DS0COLUMBUS 27 DS0 000000000 000000000 003100054 0051 000000001001000002000001000000000000000000000000000 000001000010109D20071130 0000000000016142754330 G000010109D20071130 0012009051900120090519Y11 OHIO 1N6496

    Advance thanks

    Rajan

Viewing 8 posts - 1 through 7 (of 7 total)

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