• 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
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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'
 WITH
 (FORMATFILE = 'C:\Load.fmt');

 GO
 SELECT * FROM SurveyCohort


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

30,7006149,4,09/08/2014,09/08/2014,44_Rows_2014_09_10
30,7007086,3,09/08/2014,09/08/2014,44_Rows_2014_09_10
30,7010771,4,09/08/2014,09/08/2014,44_Rows_2014_09_10

Not sure what load.fmt should look like in order to indicate value for InsertDateTime is not in csv file
0
johnnyg123
Asked:
johnnyg123
  • 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.
0
 
johnnyg123Author Commented:
The following format file got it to do what I wanted

9.0
 6
 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 ""
0
 
johnnyg123Author Commented:
The only response I got indicated there was no solution but the comment I posted did work
0

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