using bcp,how to load the data into a table in the same order as the records in the file

Question

Post reply

using bcp,how to load the data into a table in the same order as the records in the file

srihari nandamuri

Say Hey Kid

Points: 661
More actions
April 19, 2012 at 6:28 am

#96834

Hi,
While loading(using bcp) data from file into a table,I want to load the file data into a table in the same order as in the file.
Example:-
File contains data as below
a
b
c
d
and in table it should get inserted in the same order like
a
b
c
d
Please suggest.
Comments please....
Srihari Nandamuri

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

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

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 1

is there a clustered index on that table?;-)

__________________________
Allzu viel ist ungesund...

srihari nandamuri Say Hey Kid Points: 661 More actions · Answer 2

no clustered index exits on table.What I want is to load the file data as it is into the table...order of records in file should match the records in table.

I know bcp does not gaurentee the order of storage and if cluster index exist on table then it will disturb the order...

Please let me know if u have any idea...

cheers

Comments please....

Srihari Nandamuri

Geoff A SSChampion Points: 11511 More actions · Answer 3

Geoff A

SSChampion

Points: 11511

April 19, 2012 at 7:03 am

#1475987

do you have a .fmt (format) file defined?

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 4

srihari nandamuri (4/19/2012)
Hi,
While loading(using bcp) data from file into a table,I want to load the file data into a table in the same order as in the file.
Example:-
File contains data as below
a
b
c
d
and in table it should get inserted in the same order like
a
b
c
d
Please suggest.

Use BULK INSERT and OPTION(MAXDOP 1) and it will usually work just fine. Be advised that there really should be something in the file you're loading to enforce proper sorting because it's just not guaranteed without such a thing. The best thing to do would be to have the data provider provide a "row number" column in the the file.

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