Some Times that ‘s needed to bulk insert data from a text or csv file to sql server. So, as a simple example we have Products
and we create a txt file and a csv file that fill the data in order of fields above field be field and row by row.
the txt file is below
File 1.txt as you see above each row contains values for a record and values in each field of a record is seperated by , and the last field is id that the value in this field will be generated by sql server automatically. then, I entered the value 1 for id of all the records, due to the fact that id must have value to sql code could run correctly and read txt file, but after reading the text file when inserting the data the value of id will be set automatically by the sql server.
below is the sql code to insert the records in the txt file. FIELDTERMINATOR is the a character that seprates values for fields. LastRow is the last last row that must be inserted in the sql.
BULK INSERT dbo.products
FROM 'd:\1.txt'
WITH ( FIELDTERMINATOR = ',' , LastRow = 3 );
also, I create csv file to bulk Insert the data :
File 1.csv Like txt file, each row contains values for a record and values in each field of a record is seperated by , and the last field is id that the value in this field will be generated by sql server automatically. then, I entered the value 1 for id of all the records, due to the fact that id must have value to sql code could run correctly and read txt file, but after reading the text file when inserting the data the value of id will be set automatically by the sql server.
below is the sql code to insert the records in csv file. firstrow is the first row that must be inserted in the table, as you see picture above that shows file “1.csv” first row is the column header, so rows that will be inserted in the sql starts from row 2 of the “1.csv”. FIELDTERMINATOR is the a character that seprates values for fields. LastRow is the last last row that must be inserted in the sql.
BULK INSERT dbo.products
FROM 'd:\1.csv'
WITH ( firstrow=2 , FIELDTERMINATOR = ',' , LastRow = 3 );
Real Senario with large amount of Rows in real scenarios we do bulk insert for large number of rows, a problem that may be happen is if an error takes place in the process, SQL Server will rollback the whole bulk insert process. So, a solution is the to use batchsize
Therefore, We create Sales table below in our data base
Sales table Now, we want to Insert 1.500.000 recordes from csv file that we downloaded from this website. See Image below to download the csv file.
As a matter of fact, csv file is very large (187 MB), and There is not any ID field there.
Our Table Has ID field, but The csv file does not have that. Consequently, If we run bulk insert csv file (without ID Column), then we do bulk insert
Accordingly, the code to create Sales temporary table is below.
CREATE TABLE #Sales(
[Region] [varchar](50) ,
[Country] [varchar](50) ,
[Item Type] [varchar](50) NULL,
[Sales Channel] [varchar](50) NULL,
[Order Priority] [varchar](50) NULL,
[Order Date] datetime,
[Order ID] bigint NULL,
[Ship Date] datetime,
[Units Sold] float,
[Unit Price] float,
[Unit Cost] float,
[Total Revenue] float,
[Total Cost] float,
[Total Profit] float
)
The result of running code above is #sales creating temporary table
Sales temporary Table Afterwards, I should run bulk insert
BULK INSERT #Sales
FROM 'd:\1500000 Sales Records.csv'
WITH ( firstrow=2 , FIELDTERMINATOR = ',' , ROWTERMINATOR='\n' , batchsize=300000 );
The result of running query above is underneath
Now, by query beneath the records will be inserted in the sales Table.
insert into Sales
select * from #Sales
As The result, If you have look on the Sales Table (Picture Below), you see that ID Column got values for It ‘s records automatically by the system.
Sales Table with 1.500.000 records Check Constraints on bulk Insert By default, bulk insert process ignores any constraint check and foreign key constraints, but It has some exceptions. According to the Microsoft documentation “UNIQUE and PRIMARY KEY constraints are always enforced. So, any Other Constraints will not check.
For instance, We create goods Table with constraint ([price]<=(5000)) , See Picture beneath
On the condition, that you run bulk insert, constraints above will be ignored.
See file 1.txt file that price for cheese is 12000 , which is against constraint,
file 1.txt see the result of bulk insert that constraint will be ignored.
bulk insert ran successfully. Have a look at a Goods Table and see the Price constraint is ignored.
By Query beneath you can see that CK_Goods is ignored.
SELECT is_not_trusted ,* FROM sys.check_constraints where name='CK_Goods'
To force bulk insert to check all the constraints we should use check_constraints attribute.
BULK INSERT dbo.goods
FROM 'd:\1.txt'
WITH ( FIELDTERMINATOR = ',' , LastRow = 3 , check_constraints );
The result of above code
As you see; Now, the constraint check is working.
