Microsoft SQL Server BULK Import Read file using OPENROWSET(BULK) and format file

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Extensions
> Step 2: And Like the video. BONUS: You can also share it!

Example

Yu can define format of the file that will be imported using FORMATFILE option:

INSERT INTO mytable
SELECT a.* 
FROM OPENROWSET(BULK 'c:\test\values.txt',   
   FORMATFILE = 'c:\test\values.fmt') AS a;  

The format file, format_file.fmt, describes the columns in values.txt:

9.0  
2  
1  SQLCHAR  0  10 "\t"        1  ID                SQL_Latin1_General_Cp437_BIN  
2  SQLCHAR  0  40 "\r\n"      2  Description       SQL_Latin1_General_Cp437_BIN  


Got any Microsoft SQL Server Question?