• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

BULK INSERT: table has IDENTITY column

This is a general question involving BULK INSERT and format files in the case of the table having an identity column.

table:  id, firstname, lastname, title

data file row:  firstname, lastname, title

How do you handle this situation using a format file?
0
gateguard
Asked:
gateguard
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Sham HaqueSenior SAP CRM ConsultantCommented:
most straightforward solution is to BULK INSERT to an empty "staging" table containing just the cols in the load file, then do a regular INSERT to the main table from the staging table
0
 
jogosCommented:
I guess when you don't mention the Id in the format file it will be generated normaly on import
0
 
gateguardAuthor Commented:
I'll tell you right now, in my opinion there is absolutely NOTHING straight-forward about anything to do with BULK INSERT.

I have tried inserting a data file with a single row with a single field into a table with a single column and I still get errors!

Do you happen to have a sample file (with a sample table description) that you know for a fact actually works with BULK INSERT?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me ask you to put your full table creation, the format file, the file contents and the bulk insert statement you are trying to use.
the error you get looks like the data file does not match the format file.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
gateguardAuthor Commented:
I put a new sample table, data-row and error-message in the original question.

Sorry about posting follow-ups.  I thought it might be helpful to break this problem down into components.  I was wrong.
0
 
gateguardAuthor Commented:
This question is still open.  My other problem, in another related question is fixed.

But I still don't know the answer to this question.

Does anybody?
0
 
gateguardAuthor Commented:
I just used gbshahaq's method and it worked perfectly.  Thanks.
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
glad to help, gateguard.
sometimes you just have to go with the simpler options...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now