BULK INSERT DATE

Question

BULK INSERT DATE

karen.bessel

SSC Veteran

Points: 285
More actions
October 18, 2009 at 6:42 pm

#134519

I'm a SQL newbie, so pardon the question if it's stupid.
I am running SQL Server 2008 with SP1.
I've created a table using the following syntax:
Create Table SalesReps
(EmpNumTinyIntNot Null Primary Key,
NameVarchar(40)Not Null,
AgeTinyInt,
RepOfficeTinyInt,
TitleVarchar(30),
HireDateDate,
ManagerTinyInt,
QuotaMoneyDefault 0,
SalesMoneyDefault 0)
Please note that the data type of the hire date column is "DATE", not "DATETIME".
I am doing a bulk insert using the following command:
BULK INSERT SalesReps
FROM 'C:\salesreps.txt'
WITH ( FIELDTERMINATOR = '|',
ROWTERMINATOR = '' )
An example row in the source file looks like this:
107|Nancy Angelli|49|22|Sales Rep|11/14/88|108|300000.00|186042.00
When the row is inserted, the hire date comes in as 01-01-1900.
If I change the data type of that column to datetime, it works.
If I do a manual insert of that one row (ie; INSERT INTO...blah blah blah), it works.
Grrrrrr...

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 1

I'm not sure that BULK INSERT supports the DATE datatype. I've not been able to find any documentation one way or the other.

Shifting gears a bit... my recommendation is to never import directly into a final table. Instead, I recommend that you import into a temporary staging table and validate the data before letting it anywhere near a final table. That would also fix your problem because you could define the date column as DATETIME and the convert it to DATE on the insert from the staging table to the final table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karen.bessel SSC Veteran Points: 285 More actions · Answer 2

Thanks for the reply.

I wasn't aware that there were data types that aren't supported by the bulk insert. I would have thought that they all would be. Silly me. 🙂

Anyhoo, I'm using the workaround of the datetime data type.

This is homework for a class, so I'm not involved in hosing a production file, but I appreciate the advice. I'm definitely filing it away for future reference.

Thanks again,

Karen

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 3

You bet. Good luck with your training and thanks for the feedback.

I have to ask, what class are you taking and through which school? The reason I ask is because most schools don't teach BULK INSERT... sounds like a good school.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karen.bessel SSC Veteran Points: 285 More actions · Answer 4

I am actually taking this course at Collin County Community College in Frisco, TX.

It's ITSE 2309 - Database Programming.

The book that we're using is Murach's SQL Server 2008 For Developers (ISBN 978-1-890774-51-6).

The teacher made up an exercise for us that isn't part of the book, and gave us text files for the source data. Told us to create the database, tables, constraints, import the data, etc. He pointed us in the direction of the BULK INSERT and gave us one example.

I guess he was trying to weed out the students who shouldn't be there because this assignment was a fricking booger for a beginner.

I screwed up one of my foreign keys (pointed it at the wrong file) and ended up in a mess that I couldn't get out of. I deleted the database and started over (I had everything in a Word document so I didn't have to re-invent the wheel).

I spent a good amount of time trying to figure out the date problem.

So this homework assignment was not "fun". Add to that the fact that I lost my flash drive at the school last week so I'm doing some of this for the 2nd time, and you just have a barrel of laughs. LOL.

PS: Do you know if you can change an existing column to an identity column?

Jeff Moden SSC Guru Points: 1004648 More actions · Answer 5

Very cool feedback, Karen. "fricking booger" had me laughing so hard that I blew a pork chop out of my nose. 😛

Not that it will matter but tell your teacher that I like the way (s)he is teaching especially because of the intelligent questions you've asked. I wish more teachers would deviate from the dogma of a lesson plan/book to teach some real world usage.

On the identity column thing, the only way you used to be able to simply change it and still keep the original values was to alter a system table which is a real "Bozo-no-no" and I'm pretty sure you can't even do that in 2k5/2k8 anymore.

You CAN, however, create a new table, enable IDENTITY_INSERT, and copy the data. Make sure you have all the code for your indexes and keys and then drop the old table and rename the new one to the original. Then reapply your keys and the like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)