CODEWITHSUNDEEP
sqlsql-servert-sql
Llllx
Llllx

Reputation: 29

Import specific values from csv files to column of existing table

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

Upvotes: 0

Views: 2665

Answers (1)

Jaragoth
Jaragoth

Reputation: 58

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!

Upvotes: 2

Related Questions