Query regarding BULK INSERT

Question

Post reply

Query regarding BULK INSERT

harsh.dhawan

SSC Enthusiast

Points: 102
More actions
July 7, 2008 at 11:31 pm

#184303

Hi All
I have a query regarding bulk insert, I have one txt file which contains Date and Time field speprated with #. I want to insert these two fields in single column by concating the value. Also i need to calculate the the value of one more field from the value of one column in txt file.
How can i do this?
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

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

pollockk SSC Journeyman Points: 81 More actions · Answer 1

TO deal with the separator, you need to create (and use) a format file with your BULK INSERT.

Any number of web articles will help you out with this - sorry, but I haven't got any of my own examples to hand.

Format Files...

http://msdn.microsoft.com/en-us/library/ms191479.aspx

you'll need "8.0" instead of 9.0 at the top for SQL 2000

pollockk SSC Journeyman Points: 81 More actions · Answer 2

Oh and consider bulk inserting the data "as-is" i.e. in the 2 seperate fields into a staging table - then concatenating them when you load into the actual table(s).

It's also handy to write yourself a usp_BulkInsert stored proc which takes the file name, the format file name, the databse table name etc. etc. for future reuse...

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

Make your life easy... import the date#time into a single column and replace the # sign with a space in the process of converting it to a real date/time.

--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)

pollockk SSC Journeyman Points: 81 More actions · Answer 4

yeah , Jeff's suggestion would be the easiest way.