Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

BCP Load with Identiy Column

Posted on 2011-10-13
3
Medium Priority
?
258 Views
Last Modified: 2012-05-12
Hi,
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,
hefterr
0
Comment
Question by:hefterr
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36964749
You could use a Format File to specify the column mappings from the file to the table. That should work.
http://msdn.microsoft.com/en-us/library/ms191234.aspx
Section: C. Omitting a data field
0
 
LVL 1

Author Comment

by:hefterr
ID: 36965100
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>
 
Thanks,
hefterr
0
 
LVL 1

Author Closing Comment

by:hefterr
ID: 36965102
Thanks
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Screencast - Getting to Know the Pipeline
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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