Exporting data to text file

Question

Post reply

Exporting data to text file

srinadh.ramineni

Say Hey Kid

Points: 675
More actions
March 24, 2011 at 3:23 am

#236727

Hi All,
I am having more than 1 lack records. I should export these records from a table to the text file. That table is having more than 50 columns. Please advice me the best thing for exporting.
It should be fast and accurate.
Srinadh.

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

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

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 1

How many is 1 lack?

If its not millions and millions of records, the SQL Import and Export Wizard will do the trick.

srinadh.ramineni Say Hey Kid Points: 675 More actions · Answer 2

May be 1.5lacs and that too we should not use IMPORT and EXPORT wizard.

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 3

well, bcp is a bit of overkill with so little records (150 000)?

but you can export data from a table to a txt file using the bcp command:

BCP "SELECT columns FROM mytable" QUERYOUT c:\export.txt

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

you can also use openrowset

insert openrowset ('Microsoft.Jet.OLEDB.4.0', 'Text;Database=c:\export.txt')

SELECT columns FROM mytable

srinadh.ramineni Say Hey Kid Points: 675 More actions · Answer 4

But the performance is the key here. Other than BCP any alternative?

Jako de Wet Hall of Fame Points: 3123 More actions · Answer 5

well, you have 4 options that I am aware of:

SQL Import/Export Wizard

BCP

OpenRowSet

Writing a .Net/C application that will create the txt file and dump the data into a txt file.

BCP for 150 000 records shouldn't take more than a couple of seconds.

Maybe one of the other members have some other ideas.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

srinadh.ramineni (3/24/2011)
But the performance is the key here. Other than BCP any alternative?

BCP is very fast and should do fine for this task. BCP will handle exports with a very small number of rows (e.g. under 5 rows) as well exports with an extreme number of rows (e.g. into the many billions of rows).

The Import/Export Wizard could also handle this chore quite well. The Import/Export Wizard is just a name...the wizard is creating and executing an SSIS package for you.

I think with OPENROWSET the file must already exist and the setup is cumbersome.

<opinion>BCP and SSIS (Import/Export Wizard) are your best options.</opinion>

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato