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

Issue with Bulk Insert format file

I have several csv files that I'm trying to populate the following table with

CREATE TABLE [dbo].[SurveyCohort](
      [SurveyID] [int] NULL,
      [PlayerID] [int] NULL,
      [TierStatus] [int] NULL,
      [SurveyDateKey1] [date] NULL,
      [SurveyDateKey2] [date] NULL,
      [SurveyFileName] [varchar](100) NULL,
      [InsertDateTime] [datetime] NULL



ALTER TABLE [dbo].[SurveyCohort] ADD  CONSTRAINT [DF_SurveyCohort_InsertDateTime]  DEFAULT (getdate()) FOR [InsertDateTime]

The csv files contain all field values except for InsertDateTime  (I want this value to be default of getdate()  )

Since file format does not match table format I know I need to create a format file and believe the correct sql  is as follows:

BULK INSERT SurveyCohort
 FROM c:\Load_2014_08_12.csv'
 (FORMATFILE = 'C:\Load.fmt');

 SELECT * FROM SurveyCohort

here is sample data for Load_2014_08_12.csv  (no header row)


Not sure what load.fmt should look like in order to indicate value for InsertDateTime is not in csv file
  • 2
1 Solution
Russell FoxDatabase DeveloperCommented:
I don't believe you can. Your best bet is to create an interim table with just the fields in the source file, like SurveyCohortTmp. Just truncate that table before you do the bulk insert, perform the insert, then add the new records to the main SurveyCohort table which can include your default date field. It's best to truncate it before the insert rather than after because you'll have the original data already in place in the event of data issues you need to research. This is also handy for running some basic checks before adding the new records into production, like checking for empty required values or ensuring the right number of records came in.
johnnyg123Author Commented:
The following format file got it to do what I wanted

 1 SQLCHAR 0 0 "," 1 SurveyID ""
 2 SQLCHAR 0 0 "," 2 PlayerID ""
 3 SQLCHAR 0 0 "," 3 TierStatus ""
 4 SQLCHAR 0 0 "," 4 SurveyDateKey2 ""
 5 SQLCHAR 0 0 "," 5 SurveyDateKey2 ""
 6 SQLCHAR 0 0 "\n" 6 SurveyFileName ""
johnnyg123Author Commented:
The only response I got indicated there was no solution but the comment I posted did work

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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