Default Bulk Insert do not work until I noticed the file wasn't what I saw from Notepad++. Upon, retrieving the file with Notepad, the data are all in 1 single line.
Create a test table
USE [blockchain]
GO
/****** Object: Table [dbo].[blockchain] Script Date: 3/30/2018 1:12:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[blockchain](
[akey] [ntext] NULL,
[bvalue] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
select * from blockchain;
BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR =':');
It would import one row because the database all look like one row in notepad even though it looks like many rows in notepad++
From Windows Notepad, the data file looks like the following. That explain it why only 1 row get imported. The second column stores everything else.
The flat file was ported out a Linux environment where hex requires. The ROWTERMINATOR can't be set to "\r\n" but should be a hex value of "0x0a".
After changing the parameter to hex value. The following would works perfectly fine.
BULK INSERT blockchain.dbo.blockchain FROM 'C:\Users\Administrator\Desktop\blockchain\data_output2.txt' WITH ( DATAFILETYPE = 'char', FIELDTERMINATOR =':', FIRSTROW=1,ROWTERMINATOR = '0x0a')
