Bulk insert data into SQL database table
2009-04-02 Import & Export 1 159
Updating a database with new records using separate INSERT commands for each new record can sometimes take very long time, especially if there are many thousands of new records to insert. One way to speed up this process can be to use the BULK INSERT command to update the SQL database in a significantly quicker way, e.g. in 1 minute instead of 20 minutes when inserting each record separately (100 000 records).
With the function below you can upload a tab-separated text file (without headings) to an SQL data table very quickly compared to using separate SQL insert statements for each record. The source file should be in Unicode format if it contains non-English characters. Click here for information on how to create a text file in Unicode format.
After creating the tab-separated text file with the new data for the SQL server, copy the file to a folder on the SQL server, e.g. using FTP. You might also need to contact the database administrator and ask to be granted permission to run the BULK INSERT command.
Then you can use the function below to bulk load the contents into the database.
Note! You have to edit the 3 constants in the function below before using it.
Function PerformBulkUpload(cn As ADODB.Connection, strSourceFileName As String, Optional blnTruncateTable As Boolean = False) As Boolean ' strSourceFileName must be the full pathname to the file on the SQL server, e.g.: F:\FTP\USERID\NewUploadedData.txt Const cstrDataBaseName As String = "MYDATABASE" Const cstrDataBaseUser As String = "dbo" Const cstrDataBaseTable As String = "MyTableName" Dim strSQL As String, lngCount As Long PerformBulkUpload = False If cn Is Nothing Then Exit Function If Len(strSourceFileName) < 6 Then Exit Function If cn.State = adStateOpen Then PerformBulkUpload = True With cn If blnTruncateTable Then strSQL = "truncate table " & strTargetTable .Execute strSQL, , adCmdText End If If Len(strSourceFileName) > 0 Then strSQL = "bulk insert [" & cstrDataBaseName & "].[" & cstrDataBaseUser & "].[" & cstrDataBaseTable & "] " strSQL = strSQL & "from '" & strSourceFileName & "' " strSQL = strSQL & "with (DATAFILETYPE = 'widechar', FIELDTERMINATOR = '\t', ROWTERMINATOR = '
')" On Error GoTo BulkLoadError .Execute strSQL, lngCount, adCmdText On Error GoTo 0 End If .Close End With End If Exit Function BulkLoadError: MsgBox Err.Description PerformBulkUpload = False Resume Next End Function
KrisBelucci | 2009-06-02 17:12:50 (GMT)
Hi, good post. I have been wondering about this issue,so thanks for posting. I’ll definitely be coming back to your site.