SQL Server - Combining Bulk Insert with Manual value
A possible solution is to use OPENROWSET

Assuming a csv file (emp.txt):

User1,100,Dep1
User2,1000,Dep2


Assuming a format file (format_file.xml):

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




--demo setup
drop table if exists employees;
drop table if exists departments;
go
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')

--solution
--Insert data into Employees table
INSERT INTO Employees
SELECT NAME
    ,Salary
    ,DepartmentID
FROM OPENROWSET(BULK 'C:\testdata\emp.txt', FORMATFILE = 'C:\testdata\format_file.xml') emp
JOIN Departments dep ON (emp.DepartmentName = dep.DepartmentName)

--verify
select * from Employees




| NAME  | Salary | DepartmentID |
|-------|--------|--------------|
| User1 | 100    | 1            |
| User2 | 1000   | 2            |