Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005

    Unanswered: bcp and identities

    I am diving into using bcp, must have been over 10 years since I last used it.

    I want to use the TABLOCK hint for loads, I assume I want to use a format file so I can automate the export/import process?

    Source table has an identity filed which I want to discard, Destination table has an identity field which I want to use. We have up to 4 source servers containing the same data, data from these 4 different tables will be aggregated onto the destination table.

    To tackle the identity destination table issue do I just create a format file like this?

    1       SQLCHAR 0    0    ""   1   CustomerID       SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR 0    80    "\t"   2   CompanyName      SQL_Latin1_General_CP1_CI_AS
    3       SQLCHAR 0    60    "\t"   3   ContactName      SQL_Latin1_General_CP1_CI_AS
    4       SQLCHAR 0    60    "\r\n"   4   ContactTitle     SQL_Latin1_General_CP1_CI_AS
    I think I want to use native format for best performance, do I change SQLCHAR to NATIVE? or is native only used by using the -n switch? How can I do what I need to do (wrt Identity values) by using the -n switch? Would I need to import into a dummy table and then move the data to the real table? (which I would prefer not to have to do since there is a lot of data involved)

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 17
    Actually, it looks to be remarkably simple to achieve. The Identity values are controlled on the way in by the -E switch. If you leave that off, then the target table generates its own identity values (at least in SQL 2008. I have not tested earlier versions). i created a simple table like this:
    create table test1
    (id int identity(1, 1) not null primary key,
     col2 varchar(20))
     insert into test1 (col2) values ('hello')
    BCP'd the data out
    bcp applications..test1 out test.dat -n -Sservername -T
    then BCP'd the data back in to the same table.
    bcp applications..test1 in test.dat -n -Sservername -T
    Here is the result:
     select *
     from test1
     id          col2
    ----------- --------------------
    1           hello
    2           hello
    (2 row(s) affected)
    Easy peasy.

  3. #3
    Join Date
    Jun 2005
    worked like a charm, thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts