Import specific values from csv files to column of existing table

Question

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

Bulk insert the file into #temp table and the write your query. — Alex
– Alex, Commented Dec 17, 2021 at 8:59
Embrace your doom. Breaking apart a name is not the trivial task you think it is - and that is something that you must do to find the appropriate ID value to replace the supervisor name. The better question is why your file is structured in this manner - a question you and your team should be addressing first. — SMor
– SMor, Commented Dec 17, 2021 at 12:00

Jaragoth · Accepted Answer · 2021-12-17 11:06:12Z

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!

Collectives™ on Stack Overflow

Import specific values from csv files to column of existing table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related