Import specific values from csv files to column of existing table-CodePudding

I am trying to figure out how to create a query/tsql that can import values from csv file to existing table. This is example how few records of csv looks like:

LastName, FirstName, Supervisor
Michael, Scott, John Smith
Joe, Martin, Michael Scott

And my existing table in SQL Employee table All columns already have values except SupervisorId (supervisorId is a foreign key that points at main key of the same table - EmployeeId)

What I want to achieve is to write a script that will take Supervisor name from csv file, search employee table for row with given supervisor name (first name last name), take his EmployeeId and insert it into his subordinate SupervisorId column.

Is it possible to create such script? So far I have found only simple queries like:

BULK INSERT Employee
FROM 'D:\Employees.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n', 
    TABLOCK
)

But it is not even close to what I want to achieve

CodePudding user response：

As @Alex pointed out, the best way to solve this is in a few steps.

Step 1: Getting your data into a #temp table.

Step 2: Do a look up from the Employee table into your loaded data.

Step 3: Update the Employee table with your found new found information!

NOTE: Depending on the size of your data you may want to add a index to your temp tables. In addition joining on name has the chance for row expansion that you may need to contend with.

-- Step 1:
drop table if exists #tmp_Employee_Data;
BULK INSERT #tmp_Employee_Data
FROM 'D:\Employees.csv'
WITH
(
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n', 
    TABLOCK
)

-- Step 2: Using the temp table you can do your additional work. 
drop table if exists #tmp_EmpSup; 
select t.*, e.EmployeeId as SuperviserId
into #tmp_EmpSup
from Employee e join #tmp_Employee_Data t
    on e.FirstName   ' '   e.LastName = t.Supervisor

-- Step 3: Update your Table
update e
set e.SupervisorId = t.SuperviserId
from Employee e join #tmp_EmpSup t
    on e.FirstName = t.FirstName 
        and e.LastName = t.LastName

Good Luck!