Create Format File For bcp (T-SQL)


We can use bcp command to generate a format file from an existing table.


  exec master..xp_cmdshell  'bcp myDB..myTable format nul -T -n -f c:\temp\myformatfile.fmt -S myMachine\myDBInstance';

  --http://technet.microsoft.com/en-us/library/ms191516.aspx
  --http://technet.microsoft.com/en-us/library/ms191479(v=sql.110).aspx


--Sample usage of the format file to retrieve a text file using this format file

SELECT 
* 
--or column list from format file
FROM  OPENROWSET(BULK N'C:\temp\mytextfile.txt',  FORMATFILE='C:\temp\myformatfile.fmt') AS t 


 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s