Use Bulk Insert Statement with JOIN !!

[leedo]

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 = '\n' )

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

Appreciate your helps.
Thanks

[azimmer]

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 = '\n' )

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

Appreciate your helps.
Thanks

Though I'm not a BULK INSERT expert, I'd use a temp table: bulk insert into the temp table and either process it after the bulk insert is complete or put an insert trigger on the temp table that throws the inserted rows right over to the other table.

Others may be more experienced on this than me, though.

[ck9663]

Though I'm not a BULK INSERT expert, I'd use a temp table: bulk insert into the temp table and either process it after the bulk insert is complete or put an insert trigger on the temp table that throws the inserted rows right over to the other table.

Others may be more experienced on this than me, though.

i second the motion...upload it to some table. prepare all the data you need. then populated whatever table needs to be updated