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_InsertDat
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
30,7007086,3,09/08/2014,09
30,7010771,4,09/08/2014,09
Not sure what load.fmt should look like in order to indicate value for InsertDateTime is not in csv file
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_InsertDat
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
30,7007086,3,09/08/2014,09
30,7010771,4,09/08/2014,09
Not sure what load.fmt should look like in order to indicate value for InsertDateTime is not in csv file
Russell Fox
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The only response I got indicated there was no solution but the comment I posted did work