Using a Format File to Bulk Import Data in SQL Server

Posted on February 5, 2019 by Suresh Kamrushi in SQL Server

SQL Server OPENROWSET (starting with 2008) supports bulk operations through a built-in BULK provider that enables data from a file to be read and returned as a rowset. To understand it better sharing an example.
Using below example you can create table and dump data from the text file. To do that you need to create two files:
1) Text file: which holds all data that need to be imported with comma “,” separated.
2) XML file: which specify each column and their length and separator if any.

Sample data for Text File. Create a Txt file with below data and save it as “person.txt“:

1,Anthony,Grosse,1980-02-23
2,Alica,Fatnowna,1963-11-14
3,Stella,Rosenhain,1992-03-02

Sample XML file. Create an xml file with name “person.xml” and save in same folder.

<?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="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="25" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="PersonID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="FirstName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="LastName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="BirthDate" xsi:type="SQLDATE"/>
 </ROW>
</BCPFORMAT>

To create table based on text and xml file execute below query:

--Creating table
SELECT TOP 0 * INTO person
FROM ( 
SELECT TOP 1 *  FROM OPENROWSET( BULK 'FULL_PATH_TO_TEXT_FILE\person.txt', FORMATFILE = 'FULL_PATH_TO_XML_FILE\person.xml' ) ET
)A

Using above query table with person will created with all fields specified in XML. Now you need to dump the data in to that table.
Execute below query to insert all rows from text file.

---INSERTING INTO TABLES.
INSERT INTO 
	person
  SELECT  *  FROM OPENROWSET( BULK 'FULL_PATH_TO_TEXT_FILE\person.txt', FORMATFILE = 'FULL_PATH_TO_XML_FILE\person.xml' ) ET

Hope this help someone!!!

Source : https://docs.microsoft.com/en-us/sql/relational-databases/import-export/use-a-format-file-to-bulk-import-data-sql-server?view=sql-server-2017