SQL Server - Combining Bulk Insert with Manual value I have a Comma separated CSV file containing ID, FirstName, and LastName. I am trying to insert it into my existing table, however the table has few more column which the CSV file is missing thus I am wondering if it's possible to insert the CSV file combined with manual value mapping ?

Below is how my table looks like:


And this is how my CSV file looks like:


As you can see, the CSV file only contains the IdentityId column of the Table (is's called Id in the file) and Email and other fields are missing, therefore I am wondering if I can manually provide them or I need to insert the bulk item first, and then write a command to update all of them?
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            |