Sql-server – SQL Server – Combining Bulk Insert with Manual value

sql servert-sql

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:
enter image description here

And this is how my CSV file looks like:
enter image description here

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?

Best Answer

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):

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

--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            |
Related Question