Bulk Insert - column re arrangement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Bulk Insert - column re arrangement

Author

Topic

sgnerd
Starting Member

6 Posts

Posted - 2003-05-19 : 02:45:04

Dear friends,

I have to import a csv file into the database table.
But the order of the csv files is different from order
of the columns in the table.

How can i rearrange the csv file columns. For example

My table columns
c1, c2, c3, c4, c5

My csv file values are matching with column in this
way
c2, c5, c1, c3, c4

How can i match this.

Please help me. I am working with SQL server 2K on W2k

Thanks in advance.

Regards
Kumar

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-19 : 03:16:29

Kumar,

Create a view that matches the CSV and Insert into the view.

NB: Single table view only. ie: No Joins. See BOL for detail about inserting into a view.

DavidM

"SQL-3 is an abomination.."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-19 : 07:11:25

You can use a format file if you don't want to create a view. Books Online has more details on format files. They're tricky to use at first but once you get the hang of them they become second nature.

sgnerd
Starting Member

6 Posts

Posted - 2003-05-19 : 07:24:05

Can u please give me a example for format file.

Regards
kumar

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-19 : 07:41:07

You're better off looking in Books Online ("bcp" - "format files") An example won't help if you don't understand how the file is laid out.

sgnerd
Starting Member

6 Posts

Posted - 2003-05-20 : 10:29:41

Hi,
After looking at the BOL example, i tried to do that. It says

"The address and zip columns should not have field terminators and should have their field length set to 0."

So the column number becomes zero.

but while i do, i didnt get the column number a Zero. while the bcp prompts for missings columns, i done the following

storage type - I typed the datatype then
prefix length - I put 0 then
field length - also put 0
field terminator - nothing, just press 'enter' key

did i do anything wrong.

Regards
Kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources