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
Advertisement
Answer
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!