BCP Load with Identiy Column

Posted on 2011-10-13
Last Modified: 2012-05-12
I have a BCP Question.

I have define a new (empty) "Organization" table :
-  OrdId  int Identity)
-  OrgName varchar 2000
-  OrgDesc varchar 2000

Now I have a tab delimited file with just the OrgName and OrgDesc columns pulled from an EXCEL spreadsheet to load the table.

I use the following BCP command:
bcp myDB.dbo.Organization in "Organizations.txt" -T -c -m 1 -e error.txt
The identity column gets generated but the columns get all misallligned.  Do I have to leave an empty tab column for the identity column?

To work around this, I take out the identity column, load the table and add it back into the table.

Please advise how to initially load a table with data when an identity column is defined.

Thanks in advance,
Question by:hefterr
    LVL 59

    Accepted Solution

    You could use a Format File to specify the column mappings from the file to the table. That should work.
    Section: C. Omitting a data field
    LVL 1

    Author Comment

    Hi mwvisa1,
    I haven't worked with format files (yet) as I have always gotten my input to match my columns exactly.  I guess I'll have to bite the bullet.

    But I think my way wasn't too bad of a work around.  Just add the Identity column after the load.

    When I want to load a file that contains actual identity values then I think I use -E in the BCP command to retain the identity values>
    LVL 1

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now