The bulk insert command for all products in this document is:
BULK INSERT [ database_name]. [ schema_name ] . [ table_name ] FROM 'path\data_file' WITH
( FORMATFILE = 'path\*.fmt', FIRSTROW = 1, BATCHSIZE = 1000000, ROWS_PER_BATCH = 500000,
ERRORFILE = 'path\abc.log', MAXERRORS = 5000000 )Note: For bulk insert the FMT file needs to be created in SQL
server using (Microsoft SQL Server\110). The version of the bcp utility (Bcp.exe)
used to read a format file must be the same as, or later than, the version used to
create the format file. For example, SQL Server 2014bcp can read a version 10.0
format file, which is generated by SQL Server 2008bcp, but SQL Server 2008bcp cannot
read a version 11.0 format file, which is generated by SQL Server 2014bcp.
If new FMT file needs to be created, please follow below setups (https://msdn.microsoft.com/en-us/library/ms191516.aspx):
The command syntax for creating the FMT file is:
bcp [<database name>].[db].[<table_name>] format nul -c -f path\*.fmt –t " " –S –T for example,
bcp dbo.serviceability_extended_attributes format nul -c -f D:\temp\serviceability_extended_attributes.Fmt -t " " –S –T
-T: - Specifies that the bcp utility connects to SQL Server with a
trusted connection using integrated security. If -T is not
specified, you must specify -U and -P to successfully
log in.
-t " ": Specifies that the FMT file will be generated with tab delimiter
*.fmt: Specify the file name in place of * e.g., abc.fmt
Note: From the FMT file that has been generated you need to remove the
\r from it, to successfully load the data.