CODEWITHSUNDEEP
sql-server
vibhav bhavsar
vibhav bhavsar

Reputation: 197

Bulk Insert with dynamic mapping in SQL Server

Currently, I'm using Bulk Insert statement to read the CSV files and add all rows to the SQL table.

BULK INSERT tablename
FROM 'D:\Import Files\file.csv'
WITH ( 
       FIRSTROW = 2,
       FIELDTERMINATOR = ',',
       ROWTERMINATOR='0x0a');

But now I have a dynamic mapping for each file and stored in a table (File's Field Name = Database Field Name).

Mapping Table:

FileId  FileFieldName  DBFieldName

1       Order-Id       orderid
1       Order-Date     orderdate
2       Id             orderid
2       Orderedon      orderdate

I want to map the file field name with database fields and import the rows to the SQL table.

How to achieve dynamic mapping with Bulk Insert statement in SQL Server?

Upvotes: 0

Views: 1049

Answers (1)

Ross Presser
Ross Presser

Reputation: 6255

Write your dynamic map from the table to an XML file, then use this syntax for BULK INSERT:

BULK INSERT tablename
   FROM 'D:\Import Files\file.csv'   
   WITH (FORMATFILE = 'D:\BCP\myFirstImport.xml');

Upvotes: 1

Related Questions