Format File for BCP Or Bulk Insert


------bcp create non-XML format file

--One option:
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c  -f  C:\temp\myFormat2.fmt -T -t\t  -S'+ @@servername
exec master..xp_cmdshell @sql

--Another

exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul   -f  C:\temp\myFormatFile.fmt  -c -T -S myServer\MyInstance';

go


------bcp create XML format file

----Another Sample:
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\yourFORMATFIle.xml -T  -t\t -S'+ @@servername
exec master..xp_cmdshell @sql


exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\myFormatFile.xml  -T -S myServer\MyInstance';

go

--http://technet.microsoft.com/en-us/library/ms179250.aspx




 

Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
http://technet.microsoft.com/en-us/library/ms191175.aspx

Use a Format File to Skip a Table Column (SQL Server)
http://technet.microsoft.com/en-us/library/ms179250.aspx

Use a Format File to Skip a Data Field (SQL Server)
http://technet.microsoft.com/en-us/library/ms187908.aspx

XML Format file
–Simply remove the that column from section

Non-XML Format File
— Keep the leftmost row number as is (Host file field order) and change the Server coloumn order value to 0 and renumber all other columns as well.

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