BULK INSERT SQL query can be used to bulk insert data into a database table. The query imports data from a data file into a database table or view in the specified format. We can create and execute SQL query direct on SQL Server database.
Here is the BULK INSERT syntax:
  1. BULK INSERT
  2. { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
  3. FROM 'data_file'
  4. [ WITH
  5. (
  6. [ [ , ] BATCHSIZE = batch_size ]
  7. [ [ , ] CHECK_CONSTRAINTS ]
  8. [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
  9. [ [ , ] DATAFILETYPE =
  10. { 'char' | 'native'| 'widechar' | 'widenative' } ]
  11. [ [ , ] DATASOURCE = 'data_source_name' ]
  12. [ [ , ] ERRORFILE = 'file_name' ]
  13. [ [ , ] ERRORFILE_DATA_SOURCE = 'data_source_name' ]
  14. [ [ , ] FIRSTROW = first_row ]
  15. [ [ , ] FIRE_TRIGGERS ]
  16. [ [ , ] FORMATFILE_DATASOURCE = 'data_source_name' ]
  17. [ [ , ] KEEPIDENTITY ]
  18. [ [ , ] KEEPNULLS ]
  19. [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]
  20. [ [ , ] LASTROW = last_row ]
  21. [ [ , ] MAXERRORS = max_errors ]
  22. [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
  23. [ [ , ] ROWS_PER_BATCH = rows_per_batch ]
  24. [ [ , ] ROWTERMINATOR = 'row_terminator' ]
  25. [ [ , ] TABLOCK ]
  27. -- input file format options
  28. [ [ , ] FORMAT = 'CSV' ]
  29. [ [ , ] FIELDQUOTE = 'quote_characters']
  30. [ [ , ] FORMATFILE = 'format_file_path' ]
  31. [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
  32. [ [ , ] ROWTERMINATOR = 'row_terminator' ]
  33. )]
Here is SQL query example that imports data from a csv file into the Customers table.
  1. BULK INSERT Customers
  2. FROM 'C:\Data\Customers.csv';
For testing purposes, let's create a table and add data into it. If you already have a table with data, you can skip these steps.
Step 1. Create a database table. Open your SQL Server Management Studio and create a new database table.
  1. CREATE TABLE Test
  2. (ID INT,
  3. FirstName VARCHAR(40),
  4. LastName VARCHAR(40))
If you're new to CREATE TABLE, I highly recommend reading this article, Create, Insert, Update Database Table In SQL.
Step 2. Create a CSV or Text file and add this data to it in exact same oder. Save the file as test.csv in C:\ folder or whererever you like. The file has 4 rows with comma seperated values.
  1. 1,Pankaj,pandey
  2. 2,Rahul,Pandey
  3. 3,Ramesh,Mishra
  4. 4,Raja,Singh
Step 3. Write BULK INSERT SQL query and provide the table name and the full path of the .csv file.
  1. BULK
  2. INSERT Test
  3. FROM 'D:\test.csv'
  4. WITH
  5. (
  6. FIELDTERMINATOR = ',',
  7. ROWTERMINATOR = '\n'
  8. )
Execute this query on your SQL Server database.
Step 4. Check the results.
Execute the following SQL query.
  1. SELECT * FROM Test
You should see same data as it was in the csv file.
Want to learn how to implement bulk data import in a C# application? Here is a detailed article, Import Excel Data Into SQL Table Using C#