How do I BULK INSERT only one or some of the fields in the row?

Question

How do I BULK INSERT only one or some of the fields in the row?

Viewing 7 posts - 16 through 21 (of 21 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Yup - and both you and Jeff caught that. The schema.ini needs to be with the import files.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 2

EDIT: Made a boo-boo... this originally said the following and I've corrected it below this white quote box... sorry for the mistake folks. :blush:

No... not with a linked server to the text directory if the files are either comma or tab delimited... it's magic... don't need an ini file at all with delimited text files using the linked server.

No... not with a linked server to the text directory if the files are comma delimited... it's magic... don't need an ini file at all with comma delimited text files using the linked server.

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

It looks like a cute technique, so I will keep it in mind. That being said - doesn't your quote above from BOL state that there must be a schema.ini in the same folder? Or is that to mean "if there is one, THEN it must be with the files?"

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

No worky too good for me.

It added ok but when I select from the file all the fields fall under one field with the field name being the first record. I used a tab delimited file with 4 fields. I guess this would work ok if you had a file with one field and the first record contained the field name.

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

mrpolecat (3/25/2008)
No worky too good for me.
It added ok but when I select from the file all the fields fall under one field with the field name being the first record. I used a tab delimited file with 4 fields. I guess this would work ok if you had a file with one field and the first record contained the field name.

That's because I'm a bloomin' idiot, Mark... I don't know why the heck I included "tab delimited" in my post... only thing I can think of is that I just automatically typed the words because I've typed them so many times lately.

I'm going back to correct that one. Thanks for the catch. 🙂

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

Jeff Moden SSC Guru Points: 1004448 More actions · Answer 6

Matt Miller (3/25/2008)
It looks like a cute technique, so I will keep it in mind. That being said - doesn't your quote above from BOL state that there must be a schema.ini in the same folder? Or is that to mean "if there is one, THEN it must be with the files?"

Sometimes I hate BOL... I didn't even notice that. I'm thinking your "if there is one, THEN it must be with the files?" is correct.

One thing you also need to be aware of... it puts the column names in alphabetical order (if not present in all, subs "Fx" where "x" is a number). OpenRowSet seems to work better.

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

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

Do you need the first record to contaian the field names or does it automatically use col001, col002 ...