Bulk Insert Statement With JOIN !!!

Hi,

I have two tables. Employees and departments as follows:

Employees
- Name
- Salary
- Department ID


Departments
-Department ID
-Department Name


Now what I am doing is that I am allowing the user to give me a CSV file with all the employees data to bulk insert it in the table Employees as follows:

Employee Name, Salary, Department Name


The problem is that the users know nothing about the department ID. So what they type in the CSV ffile as you can see above is the department name.

The SQL statement I normally use for bulk insert is:


BULK INSERT CAS.dbo.employees
FROM 'c:list.csv' WITH
(FIELDTERMINATOR = ',', ROWTERMINATOR = '
' )

My question is, how can I use the same technique but insert the departments IDs not name as in the CSV file.!!!!

Appreciate your help.
Thanks