Solved

Issue with Bulk Insert format file

Posted on 2014-09-12
3
132 Views
Last Modified: 2014-09-20
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
Comment
Question by:johnnyg123
  • 2
3 Comments
 
LVL 13

Expert Comment

by:Russell Fox
ID: 40320455
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
 

Accepted Solution

by:
johnnyg123 earned 0 total points
ID: 40324011
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
 

Author Closing Comment

by:johnnyg123
ID: 40334229
The only response I got indicated there was no solution but the comment I posted did work
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now