Bulk Insert in SQL (Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server)

I was working on one task in which I need to insert thousands of record  in database . I have a txt file in which pipe separate values are there, My file format is like this

Coin_ID | Customer_ID | Coin_Address | BitCoins | Status_ID | Is_Active | Created_By | Updated_By | Updated_Date | Transaction_ID | Transfer_Type


7635 | 209285 | 1HqrLzm2jUqcNGi4YQtMstUcadNBzfuz39 | 0.019 | 4 | 1 | Finance | Finance | 06/08/2018 02:31:54 PM | 334fb8d949d4f2783060cac4ac238e13bb12gfgf3ce3c4943d9f8f5639e88 | 1


One thing bulk will not support decimal values so the table you make that column as float

and number of columns should be the same as in csv and in table otherwise you get an error check last link

So my table format is like this

CREATE TABLE [dbo].[test_auto](
[Coin_ID] [int] NULL,
[Customer_ID] [varchar](50) NULL,
[Coin_Address] [varchar](100) NULL,
[BitCoins] float NULL,
[Status_ID] [int] NULL,
[Is_Active] [int] NULL,
[Created_By] [varchar](50) NULL,
[Updated_By] [varchar](50) NULL,
[Updated_Date] [datetime] NULL,
[Transaction_ID] [varchar](100) NULL,
[Transfer_Type] [int] NULL
) ON [PRIMARY]
GO

There are two ways you can make SP and pass the value or if you are using only one time then use the normal script . i will share both of them

Simple Script :

BULK
INSERT test_auto
FROM 'C:OutputFile_08062018143155.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
FIRSTROW=2

)
let me explain line by line
I have create a table with the name of test_auto in which i have to insert my all values after that i define my path where is my file located.Third thing FIELDTERMINATOR  is a parameter in which you define what value separate the two words right now mine was pipe sign you can use what ever you want. ROWTERMINATOR  is end of the row .FIRSTROW is  which line you want to read my first row is my heading so I want to start from second row.

Store Procedure:

CREATE PROC BulkInserttest (@FilePath VARCHAR(100), @FileFieldterminator VARCHAR(5))
AS
BEGIN
SET NOCOUNT ON;
--Truncate the existing data from table
TRUNCATE TABLE dbo.test_auto
--Declare the local variable
DECLARE @bulkinsert NVARCHAR(2000)

SET @bulkinsert = 
   'BULK INSERT dbo.test_auto
   FROM ''' +  @FilePath + '''
   WITH
   (
    FIRSTROW = 2,
    FIELDTERMINATOR = '''+ @FileFieldterminator +''',
    ROWTERMINATOR = ''\n''
   )'
--Execute the BULK INSERT statement    
EXEC (@bulkinsert)
--Print the number of rows affected
PRINT CAST(@@ROWCOUNT AS VARCHAR(5))+ ' Rows Inserted'
RETURN (@@ROWCOUNT)
--The number of rows affected by a Transact-SQL statement will return thereafter
SET NOCOUNT OFF;
END
GO

Now if you want to run this :

exec BulkInserttest  'C:OutputFile_08062018143155.txt','|'

Refer links:
Other Parameters
Help
Bulk Insert with Decimal values

https://stackoverflow.com/questions/5475729/bulk-upload-unexpected-end-of-file-on-new-server