bulk insert question

Question

Post reply

bulk insert question

M-301385

SSC Veteran

Points: 298
More actions
October 26, 2006 at 9:56 am

#103260

Does bulk insert use bcp at all to load data? I need to load some info into a table from a text file, but have been told to not use bcp.
Thanks for your time!

Viewing 8 posts - 1 through 7 (of 7 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I don't believe that it does. Just curious though, who told you not to use bcp and why?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

M-301385 SSC Veteran Points: 298 More actions · Answer 2

I was told that it doesn't work well in SQL server 2005 and that it will be completely phased out by the following release.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

The entire DTS module is also being re-written in SQL Server 2005 into SSIS. Bulk Insert is part of DTS and will be included in the re-write. Both Bulk Insert and bcp work well in SQL Server 2000 in my opinion.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

M-301385 SSC Veteran Points: 298 More actions · Answer 4

How about openrowset()? I've tried using it but Im running into some error when I try to compile.

I've tried using this:

INSERT INTO TABLE_A

SELECT * FROM OPENROWSET

(BULK 'S:\DATA\SUB\sub.txt')

Where S: is a network drive. Is this a bad idea? Would it be better to use the UNC name? I get the same error regardless.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'BULK'.

Thanks!

Richard Josey Ten Centuries Points: 1076 More actions · Answer 5

According to BOL this is not how you use OPENROWSET.

I am presuming here that TABLE_A has multiple columns and the data file represents this in a suitable format like .csv

You can use BCP to create a format file which specifies the mapping between file and table.

Then you can try BCP, OPENROWSET and BULK INSERT. The main difference seems to be that BCP runs out of process (as far as SQL Server is concerned).

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 6

Bulk insert works like a champ. bcp is a command-line utility that also works well if you are extra special nice to it. Search for either topic in the forums (fora?) on this site and you should get loads of useful info. BOL is pretty accurate as well.

There is no "i" in team, but idiot has two.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

BULK INSERT can also use a format file for complex imports. For simple imports that all use the same column delimiter, you don't even need that... Bulk Insert is a little faster than BCP because BCP is actually a very robust probgram that does all sorts of error checking behind the scenes.

It'll be a sorry day if they ever get rid of BCP.

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