Bulk Insert using format file, mapping one input column to two output column

Question

Bulk Insert using format file, mapping one input column to two output column

Charbel Melhem

SSC Journeyman

Points: 92
More actions
March 20, 2007 at 4:58 am

#76674

Hi,
I am using bulk insert to import a text file into a Table.
The text file has less columns than the table. I need to import the same column into two different table fields.
example:
input file:
field1,field2,field3
aa,bb,cc
Table fields:
field1,field2,field3,field4
aa,bb,bb,cc
I need to map as follows:
field1:field1
field2:field2
field2:field3
field3:field4
Can I do this using Bulk insert and format file
Thanks

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Charbel

I think you can, but, depending how much data you are importing, you may find it easier to import into a staging table that has the same columns as the text file, copy the data to your "live" table, then delete or empty the staging table.

John

Charbel Melhem SSC Journeyman Points: 92 More actions · Answer 2

Yes John, Thanks for your suggestion.

I am importing around 500,000 records with every record with 1000 character wide(100 fields).

So I can do what you sugeested but this will almost double the load time.

Can you tell how to do that without using a staging table.(i.e mapping one input field to 2 table fields).

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Charbel

If you read the BULK INSERT topic in Books Online, that should tell you everything you need to know.

John

Charbel Melhem SSC Journeyman Points: 92 More actions · Answer 4

Hi John,

Actually I checked the online books, they explain how to do when the source and destination have diff number of fields or when the order is diff. but they don't expalin how to map 1 input field to 2 output fields.

your support would be very appreciated.

Thanks,

Charbel

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Charbel

This is from the Microsoft website:

To create a format file that is easily modified, start the BCP

utility by using the following command line:

bcp <db_name>.<owner>.<table> out <data_file> /S<server> /Usa /P<password>

When you start the program, a series of questions is asked. Choose

all the default options. When prompted to save the information in a

BCP format file, press Y, then ENTER. Then edit the format file to

have data types of "sybchar" and a prefix length of 0 (zero), and

enter the proper length and/or field terminators.

After this process is completed, run the BCP utility with the

/f <bcp.fmt> option. This option tells BCP to use the specified BCP

format file.

Once you have created the format file as above, you should be able to edit it so that one of the fields is repeated. Then you will be able to do the bulk insert.

John

Charbel Melhem SSC Journeyman Points: 92 More actions · Answer 6

Thanks john,

This what i am doing to produce the format file.

But when trying to map 1 input field to 2 output fields, it's not working, or at least i am not doing it right.

Could you please give me an example on how to do it using my Example shown above.

Thanks again for ur hep.

Charbel

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

Charbel

I've never tried doing anything like that before, so I don't know exactly how to do it. One way round it would be to edit your format file as described in the Books Online topic Using a Data File with Fewer Fields, do your bulk insert, and then do UPDATE MyTable SET field3 = field2.

John

Charbel Melhem SSC Journeyman Points: 92 More actions · Answer 8

Thanks John,

That's what I was trying to aoid, as it takes time and make the log file very big.

Thanks anyway