How to Upload CSV File into database MSSQL

Brief Description

The post will show code example how to upload CSV file data into Database and insert bulk record into SQL server database.

Before we start the example, create the table in the database :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CSVTABLE](
    [ID] [int] NOT NULL,
    [NAME] [varchar](40) NOT NULL,
    [ADDRESS] [varchar](80) NOT NULL,
    [POSKOD] [varchar](15) NOT NULL,
    [NOTEL] [varchar](15) NOT NULL,
 CONSTRAINT [PK_CSVTABLE] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

After create the table on the database, now we can proceed on the code example. Let say the csv file like this :

The CSV File

1,NAMA1,ADDRESS1,POSKOD1,NOTEL1
2,NAMA2,ADDRESS2,POSKOD2,NOTEL2
3,NAMA3,ADDRESS3,POSKOD3,NOTEL3
4,NAMA4,ADDRESS4,POSKOD4,NOTEL4
5,NAMA5,ADDRESS5,POSKOD5,NOTEL5
6,NAMA6,ADDRESS6,POSKOD6,NOTEL6
7,NAMA7,ADDRESS7,POSKOD7,NOTEL7
8,NAMA8,ADDRESS8,POSKOD8,NOTEL8
9,NAMA9,ADDRESS9,POSKOD9,NOTEL9
10,NAMA10,ADDRESS10,POSKOD10,NOTEL10
11,NAMA11,ADDRESS11,POSKOD11,NOTEL11
12,NAMA12,ADDRESS12,POSKOD12,NOTEL12
13,NAMA13,ADDRESS13,POSKOD13,NOTEL13
14,NAMA14,ADDRESS14,POSKOD14,NOTEL14
15,NAMA15,ADDRESS15,POSKOD15,NOTEL15
16,NAMA16,ADDRESS16,POSKOD16,NOTEL16
17,NAMA17,ADDRESS17,POSKOD17,NOTEL17
18,NAMA18,ADDRESS18,POSKOD18,NOTEL18
19,NAMA19,ADDRESS19,POSKOD19,NOTEL19
20,NAMA20,ADDRESS20,POSKOD20,NOTEL20
21,NAMA21,ADDRESS21,POSKOD21,NOTEL21
22,NAMA22,ADDRESS22,POSKOD22,NOTEL22
23,NAMA23,ADDRESS23,POSKOD23,NOTEL23
24,NAMA24,ADDRESS24,POSKOD24,NOTEL24
25,NAMA25,ADDRESS25,POSKOD25,NOTEL25

save the csv file as Book1.csv at Drive D:\ . So now run the script in sql server to insert all records as a bulk copy into database .

The Script sql statement

USE Testing123  

BULK
INSERT CSVTABLE
FROM 'D:\Book1.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

The Output

By Mohd Zulkamal
NOTE : – If You have Found this post Helpful, I will appreciate if you can Share it on Facebook, Twitter and Other Social Media Sites. Thanks =)