Thread: Bulk Insert with other requirements..

Hi guys, I have a code here that inserts a .csv file to an sql server 2005 database.

Code:

Bulk insert Sampletb
from 'C:\Sample\Sample.csv'
with
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

But it directly inserts all the contents of the file to the database.. Want I want to do is to call the Ids from a different table and insert it instead of the name in the a certain field.

Example:

Code:

-Sample.csv-
Name        City     Type
====       ====    ====
Name1     City1    Type1
Name2     City2    Type2


-Typetb (in the database)-
TypeId   Type
=====   ====
1          Type1
2          Type2
3          Type3

-Sampletb(When inserted to the database)-

Id       Name    City     Type
====  ====    ====    ====
1       Name1   City1       1
2       Name2   City2       2

Any ideas?? Thanks.

Originally Posted by Wolffy

Probably the easiest way I can think of is to do the Bulk Insert into a temp table and then update your Sampletb from that.

A more complicated method would be to INSERT with an OPENROWSET. For more info on that look here Importing Bulk Data by Using BULK INSERT or OPENROWSET(BULK...). Something like the following might work:

Code:

insert into SampletB(name, city, type)
SELECT    t1.name, t1.city,  t2.typeID
FROM    OPENROWSET
    ( 
        BULK 'd:\work\test.txt', 
        FORMATFILE = 'd:\work\testImport-f-n.Fmt'
    ) AS t1(name, city, type)
Join Typetb t2
 on t1.type = t2.type

Info on the FormatFile is here: Using a Format File to Bulk Import Data

Thank you so much for the suggestion!! Will try this ASAP...