Post reply

Help with BCP OUT/IN Operation when a column gets changed to "NOT NULL"

  • October 31, 2010 at 11:31 pm

    #230604

    Dear gurus,

    I am having issues with doing a BCP OUT/IN on an recreated table.

    I have table that have millions of records and I am hoping to BCP OUT the records, recreate the table, and do a BCP IN. My commands are simply:

    BCP DBServer.dbo.myTable OUT myFlatFile.out -T -n

    Consequently, the BCP IN:

    BCP DBServer.dbo.myTable IN myFlatFile.out -T -n -E

    Now, my problem is on the BCP in. Oh, when i recreate the table, one of the column becomes a not nullable column, wherein originally it was. However, that column are all populated. But the problem that occurs when i BCP in it is that, there's an error that the data is truncated and thus, nothing gets imported.

    When i try to recreate the table and leave that column as NULLABLE, the data gets imported fine. However, I am intending to use that column as part of the PRIMARY KEY and thus, it needs to be NOT NULL during the create. What can I do to be able to import this data to the newly created table?

    Thank you very much for any assistance.

  • November 1, 2010 at 12:08 am

    #1244716

    Hmmm... I tried it several times, but it looks like the problem was with my -n parameters. I used it because there is a column that is an XML field. But it looks like i can simply use the -c.

    So i replace -n with -c and it works. 🙂

    Sometimes you just need to write the problem, and then the head becomes clearer to see the other options.

    Thanks!

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

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