Post reply

BCP native format data issue on import

  • January 25, 2013 at 7:27 am

    #273411

    I am using bcp in native format to export a products table out

    which has an integer primary key (NOTE it is not an IDENTITY) but with gaps in the id.

    i.e i have 5878 rows and the product id at row 5878 stands at 6008

    however when importing the data back on a different sql server... the product id is being

    reset totally sequentially getting rid of the gaps in the id that were present

    in the key so the max product id at row 5878 is now 5878...

    and all product id's now match all the row numbers

    the total rowcount is 5878 in both cases...

    I cannot understand it as i am doing a native export and import

    which is the defacto way of transferring sql server to sql server

    i've checked it out a few times and it is still doing it

    sql as below

    select max(productsid) from products_target 5878

    select max(productsid) from products_source 6008

    select count(*) from products_target 5878

    select count(*) from products_source 5878

    any ideas ?

  • January 28, 2013 at 9:00 am

    #1581671

    Hi All,

    I've checked the target tables again and apologise but they do have identity fields...and that is what is causing the problem..the "bcp in" just generates sequential keys for the id column ignoring the source data for the id. I thought in native format the data being binary the bcp would ram home the data for this column. Any one know of setting the CHECK_IDENT_INSERT on type of flag (if there is one) for bcp ...

    rgds

    robin

  • January 28, 2013 at 9:05 am

    #1581674

    run bcp /? from the command prompt for all the possible flags;

    i think the one you want is the -E flag:

    -E keep identity values

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 28, 2013 at 9:33 am

    #1581689

    thanks Lowell..it checks out

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

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