The Bulk Insert in SQL Server (shortly called as BCP) will be very helpful in quickly transferring a large amount of data from a Text File or CSV file to a Server Table or Views.
BULK INSERT in SQL Server example
In this SQL Server 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 SQL Server Bulk Insert statement.
Our SQL Server table is empty as shown below:
In order to transfer the data present in the text file or CSV file to SQL table, First, Please open your Management Studio and write the following query
-- Example for BCP in SQL Server 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 Management Studio and check the destination table. We could successfully copy the data from a text file to the SQL server or not using this Bulk insert.