BULK INSERT with a format file

Question

BULK INSERT with a format file

kevaburg

SSCoach

Points: 18131
More actions
March 5, 2015 at 5:06 am

#295019

Hi all,
I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that I have no idea how to create one. Can anyone offer any help help or know of any good resources?
Thanks and regards,
Kev

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

This seems to be what you're after: https://msdn.microsoft.com/en-us/library/ms179250.aspx?f=255&MSPPError=-2147217396

If you want an alternative method, the simplest thing I can think of is to create a view with 12 of the 14 columns you have in your table and then bulk insert into the view.

-- Gianluca Sartori

kevaburg SSCoach Points: 18131 More actions · Answer 2

Hi Gianluca,

brilliant! Thanks for the link. I'll post again with my solution.

As for your suggestion......

It would be a good one in most cases but unfortunately for us it breaks our business logic as the 2 missing fields are automatically inserted during the loading process. It could work for us if I bulk insert into a temporary table and then move the data from there into where it is supposed to be but that adds an unnecessary complexity that isn't desireable......

Many thanks for your help!

Regards,

Kev

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

spaghettidba (3/5/2015)
This seems to be what you're after: https://msdn.microsoft.com/en-us/library/ms179250.aspx?f=255&MSPPError=-2147217396
If you want an alternative method, the simplest thing I can think of is to create a view with 12 of the 14 columns you have in your table and then bulk insert into the view.

+1000. The view is a great alternative to a format file for such a thing.

For how to do the format file for such a thing, some guy wrote an article that looks kinda easy to understand. Here's the link.

http://www.sqlservercentral.com/articles/BCP+(Bulk+Copy+Program)/105867/

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