#title OpenRowSet-Bulk
[[TableOfContents]]

맨날 까먹는다. 이거..
{{{
SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleAsciiCode.txt', SINGLE_CLOB) MyFile

SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleUnicode.txt', SINGLE_NCLOB) MyFile

SELECT BulkColumn 
FROM OPENROWSET (BULK 'c:\temp\SampleXML.xml', SINGLE_BLOB) MyFile

SELECT * 
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0', 'text;Database=C:\;HDR=NO', 'SELECT * FROM sample.txt')
}}}


http://sqlblog.jfperry.com/sqlblog/post/2008/10/05/Open-a-text-file-using-OPENROWSET-and-OPENDATASOURCE.aspx


포멧파일을 만든다..
bcp [데이터베이스명].[스키마명].[테이블명] format nul -T -n -t"0x1c" -f C:\format1.fmt
bcp AdventureWorks.Sales.Currency format nul -T -c -t"0x1c" -x -f Currency.xml


대충 이런 모양..
{{{
9.0
10
1       SQLDATETIME   0       8       "\t"   1     LoginTime                ""
2       SQLDATETIME   0       8       "\t"   2     LogoutTime               ""
3       SQLINT        0       4       "\t"   3     GameCode                 ""
4       SQLCHAR       0       12      "\t"   4     CN                       Korean_Wansung_CI_AS
5       SQLNCHAR      2       24      "\t"   5     LoginID                  Korean_Wansung_CI_AS
6       SQLINT        0       4       "\t"   6     Playtime                 ""
7       SQLINT        0       4       "\t"   7     Age                      ""
8       SQLCHAR       0       1       "\t"   8     Gender                   Korean_Wansung_CI_AS
9       SQLINT        1       4       "\t"   9     Glevel                   ""
10      SQLINT        1       4       "\r\n"   10    Gchannel                 ""
}}}

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