Bulk Copy SQL Server Data

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Intelligent Solutions Inc.

Version Compatibility:

More information:

Refer to comments for usage and explanation.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

Public Function BCP(ByVal Server As String, ByVal UserName As _
    String, ByVal Password As String, ByVal Database As String, _
    ByVal Table As String, ByVal FileName As String, _
    Optional Import As Boolean) As Boolean

'**************************************************************
'PURPOSE:       Do a SQL Server BulkCopy
'ASSUMPTIONS:   You understand what a bulk copy is; otherwise
'               consult SQLServer books online
'PARAMETERS:    Server:     ServerName
'               UserName:   UserName for Server
'               Password:   Password for Server
'               Database:   Name of Database
'               Table:      Table Name or SQL Statement for BCP
'               FileName:   File Name to Import from or Export To
'               Import:     If set to true, works as an import 
'                          ("in") Otherwise, defaults to export
'                          ("out")
'RETURNS:       True if successful, false otherwise
'EXAMPLE:       
'BCP "MySQLServer", "username", "pwd", "MyDataBase", "MyTable", _
    "C:\MyTable.out"
'
'Bulk Copies all rows of MyTable in MyDatabase on MySQLServer
'to a file named C:\MyTable.out
'REQUIRES:      A reference to Microsoft SQLDMO Object Library
'LIMITATIONS:   -- Assumes SQL Server Authentication as opposed
'               to NT integrated authentication.
'
'               -- Only allows full tables to be 
'               imported/exported i.e., you cannot specify 
'               where criteria
'
'               -- Uses mostly default options of bcp.
'               If you want to set more options, the BulkCopy
'               object permits this. Refer to documentation or
'               object browser
'***********************************************************

Dim objServer As New SQLDMO.SQLServer
Dim objBCP As New SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database

'if file doesn't exist and it's an import,
'don't waste time
If Import = True And Dir(FileName) = "" Then Exit Function

On Error GoTo ErrorHandler
objServer.Connect Server, UserName, Password
objServer.EnableBcp = True

Set objDB = objServer.Databases(Database)
With objBCP
    .DataFilePath = FileName

'Below speeds things up
    'but does not log the bulk copy operation
    'comment out if this is not what you
    'desire
    .UseBulkCopyOption = True
End With

If Import Then
    objDB.Tables(Table).ImportData objBCP
Else
    objDB.Tables(Table).ExportData objBCP
End If

BCP = True

ErrorHandler:
    Set objBCP = Nothing
    Set objServer = Nothing
End Function