In this tutorial, we are going to discuss two ways to load data from a csv file into a SQL Server database table -
1) BULK INSERT
SQL -
Create table dbo.Demo
(ID int null,
Product varchar(100) null);
BULK INSERT dbo.Demo
FROM 'C:\SQL\Files\Test.csv'
WITH (FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
BATCHSIZE = 25000,
MAXERRORS = 2);
Select * from dbo.Demo
2) OPENROWSET
SQL
Select * into dbo.Demo1
from
OPENROWSET( BULK 'C:\SQL\Files\Test.csv' ,
FORMATFILE = 'C:\SQL\Files\Test.fmt',
FIRSTROW = 2) as a;
Select * from dbo.Demo1
Sample Format File - test.fmt
Below are the details -
11.0
2
1 SQLCHAR 0 7 "," 1 ID ""
2 SQLCHAR 0 100 "\r\n" 2 Product SQL_Latin1_General_CP1_CI_AS