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.us_address_fabric format nul -c -f D:\temp\us_address_fabric.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.
Alternate command for bulk insert:
BULK INSERT <Table_name>
FROM '<Data_Path>'
WITH (FIRSTROW = 2,
FIELDTERMINATOR = '|',
ROWTERMINATOR='\n',
BATCHSIZE=1000000);Example:
BULK INSERT [dbo].[address_fabric]
FROM
'E:\ADDRESS_FABRIC_USA_202303_TXT\data\address_fabric_usa.txt'
WITH (FIRSTROW = 2,
FIELDTERMINATOR = '|',
ROWTERMINATOR='\n',
BATCHSIZE=1000000);