?
Solved

Issue with Bulk Insert format file

Posted on 2014-09-12
3
Medium Priority
?
146 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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

770 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