cancel
Showing results for 
Search instead for 
Did you mean: 

How I can generate a bcp ordered of a table with index non-clustered?

Former Member
0 Kudos

Hi Community:

I need know how I can generate a bcp ordered of a table with index non-clustered. This without create a index Cluster. I need other way.

Thanks!

Cristian Lopez,

Accepted Solutions (1)

Accepted Solutions (1)

0 Kudos


Hello,

the best way would be to create a view, where you force your index:

create view <viewname> as select * from <table> (index <yourindexname>)

go

then just bcp out this view.

There is no direct way to do this with bcp.

Regards

Stefan

former_member182259
Contributor
0 Kudos

The other way is to simply run the query via isql and specify -s and -h0 (I think -h0 turns off headers) and use set nocount on prior to query.... bcp out is a simple select - nothing fancy....so by putting the query in a file and using -i and -o for in/out files and the other options, no view likely necessary.  If the header is still there a quick sed script or a unix head command can strip it.

...of course this will be character mode....bcp utility supports native mode, which is infinitely better for floats, etc.

Former Member
0 Kudos

Hi Jeff Tallman

But the data is generated in order so there is no clustered index?

Thanks!

former_member182259
Contributor
0 Kudos

I would assume the query would have an order by clause.

Answers (1)

Answers (1)

Former Member
0 Kudos

Try using --initstring

e.g.

bcp '#t1' out /tmp/t1.txt -Usa -P`cat .sa1` -Smysrv1 -c --initstring "select * into #t1 from master..spt_values where type='P' order by number desc"

0 Kudos

Sorry, but the initstring won't help, cause the bcp out will grab the tabledata not the result data of your select.

The --initstring is helpful if you want to set an option, example: bcp ... in ... --initstring 'set logbulkcopy on'.