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 = '\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 help.
Thanks

If you use SQL Server 2005 you could use OPENROWSET function with BULK option. OPENROWSET if Table-value function, so you could use it wiht JOIN. Then use INSERT INTO:

Code Snippet

create table Employees(

Name nvarchar(20),

Salary decimal,

DepartmentID int

)

go

create table Departments

(

DepartmentID int,

DepartmentName nvarchar(20)

)

go

insert into Departments values(1,'Dep1')

insert into Departments values(2,'Dep2')

--Select only

select Name, Salary, DepartmentID

from OPENROWSET(BULK 'C:\emp.txt', FORMATFILE='C:\format_file.txt') emp

JOIN Departments dep on (emp.DepartmentName=dep.DepartmentName)

--Insert data into Employees table

insert into Employees

select Name, Salary, DepartmentID

from OPENROWSET(BULK 'C:\emp.txt', FORMATFILE='C:\format_file.txt') emp

JOIN Departments dep on (emp.DepartmentName=dep.DepartmentName)

My CSV file:

Code Snippet

User1,100,Dep1
User2,1000,Dep2

My format file:

Code Snippet

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/formathttp://schemas.microsoft.com/sqlserver/2004/bulkload/format">http://schemas.microsoft.com/sqlserver/2004/bulkload/format</A< A>>" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
'>http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
; <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="Salary" xsi:type="SQLDECIMAL"/>
<COLUMN SOURCE="3" NAME="DepartmentName" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>

|||Thanks, that does it.

Cheers