• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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