Read the entire flatfile into a binary data stream, and you need to use your own cast+substring to intercept the data as a field.
SELECT *, CAST (bulkcolumn as varchar (max)) from OPENROWSET (BULK N ' D:\Test.txt ', single_blob) as Document;
Another way to do this is to use formatfile, so that the Select data field is displayed in the format defined by the FormatFile.
SELECT a.* from OPENROWSET (BULK ' D:\Test.txt ', formatfile = ' D:\test.fmt ') as A;
How did the formatfile come about?
BCP JerryDB.dbo.ssis_merge_test format nul-c-F D:\test.fmt-S Jerrychenlap\mssqlserver2014-t
The above command line generates the following data to D:\test.fmt this file
10.0
3
1 SQLCHAR 0 "\ t" 1 col1 ""
2 SQLCHAR 0 "\ t" 2 col2 chinese_prc_ci_as
3 SQLCHAR 0 "\ r \ n" 3 col3 ""
Reference:
Create a format file
https://technet.microsoft.com/zh-cn/library/ms191516 (v=sql.90). aspx
OPENROWSET (Transact-SQL)
Https://msdn.microsoft.com/zh-cn/library/ms190312.aspx
Bulk import of data using a format file
https://technet.microsoft.com/zh-cn/library/ms178129 (sql.90). aspx
Use OPENROWSET (BULK ...) Load data from a flat file