Solved

Issue with Bulk Insert format file

Posted on 2014-09-12
3
144 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

689 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