BULK INSERT in SQL Server

The Bulk Insert in SQL Server (shortly called BCP) will be very helpful in quickly transferring a large amount of data from a Text File or CSV file to a Table or View.

BULK INSERT in SQL Server Example

In this Bulk Insert example, we will show you how to transfer the data present in the text file to the table.

We have a flat file called GEOGRAPHY.txt containing 1000000 Rows. Our task is to insert all the rows present in this text file using the Bulk Insert statement.

Text File 1

Our SQL Server table is empty, as shown below. For more information, check our SQL Server tutorial page.

Empty Table 2

In order to transfer the data present in the text file or CSV file to the table, First, Please open your Management Studio and write the following query.

TIP: Please refer to the SQL INSERT Statement and SQL INSERT INTO SELECT. Also, refer to the SQL SELECT INTO statements.

BULK INSERT [DimGeography] 
      FROM 'F:\MS BI\FILE EXAMPLES\Geography.txt' 
  WITH  
    ( 
       DATAFILETYPE    = 'char', 
       FIELDTERMINATOR = ',', 
       ROWTERMINATOR   = '\n' 
    );
Messages
-------
(1000000 row(s) affected)

If you want to send the data in multiple batches, then use ROWS_PER_BATCH

Let us open the SQL Server Management Studio and check the destination table. We could successfully copy the data from a text file to the Server table, or not use this Bulk insert.

BULK INSERT Example 6
Categories SQL

Comments are closed.