Bulk insert is used to Import a data file into a database table or view in a user-specified format. It's not daily using kind of code, so it's very common question on most of the site like. How to import CSV file into a table? How to insert records from a CSV file to a table, delimited by comma? How to load CSV file to SQL Server database? We will discuss here the same but with more detail, so let's start
Suppose we have a CSV file of car detail, which we want to import into our CarDetail table
2008,Acura,MDX,4dr SUV 4WD,70257 2010,Audi,A5,2dr Cabriolet,10150 2008,Audi,A8,L 4dr Sedan,900 2010,BMW,3-Series,4dr Sedan,2700 2010,BMW,X5-Series,4dr SUV,1700
First of all we need a table CarDetail, so let's create it.
Create Table CarDetail ( Year int, Make varchar(50), Model varchar(50), Body varchar(50), Odometer int )
Before writing the code to insert record, one thing we need to remember, our CSV file should be on the server if we are using the drive name, as I am going to use. Now run the following script to insert records into our car detail table.
BULK INSERT CarDetail FROM 'C:\CarDetailCSV.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO
If there would be any error in any line that record will not be inserted but the rest will. Now you can check your car detail table
Select * From CarDetail
Now let's say your CSV file have header row as well and you don't want to insert the header, but you want to insert record from second row by skipping first row, see this file
Year,Make,Model,Body,Odometer 2008,Acura,MDX,4dr SUV 4WD,70257 2010,Audi,A5,2dr Cabriolet,10150 2008,Audi,A8,L 4dr Sedan,900 2010,BMW,3-Series,4dr Sedan,2700 2010,BMW,X5-Series,4dr SUV,1700
So how to skip first row, it's easy use FIRSTROW = n, where n is the row number from where you want to insert record, nth row will be treated as first row.
BULK INSERT CarDetail FROM 'C:\CarDetailCSV.txt' WITH ( FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO
Suppose our CSV file is huge, which will take time to insert all records so I want to test with inserting from 5th to 50th record only, is that possibe? Yes, it is, in that case we need to use FIRSTROW =5 and LASTROW = 50 like this
BULK INSERT CarDetail FROM 'C:\CarDetailCSV.txt' WITH ( FIRSTROW = 5, LASTROW = 50, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|By Ali Adravi On 08 Feb, 13 Viewed: 15,846|
Bulk insert with text qualifier from a text or csv file into sql server can be achieved by using a format file, in format file we can define the sequence of columns in source file (txt or csv file), data type, length, destination table column, version and number of columns, we will see everything... By Ali Adravi On 10 Jun 2013 Viewed: 18,376
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 2,199
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 560
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 10,642
TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi On 28 Dec 2012 Viewed: 497