How do I use the SQLBulkCopy Class to transfer data from Excel to SQL server

I have seen a ton of information on using the SQL BulkCopy Class to transfer data from Excel to SQL server but for the life of me I am just not getting it. I have tried to change the code that I have found but to no avail. I am brand new to SQL and I am using the Express edition that comes with the latest version of Visual Studio. Okay here is what I am trying to do. I have an old Access db that I need to transfer to a SQL backend. All of the data that I need to move into the SQL tables are in Excel spreadsheets. I have a SQL table called Collinfo that has 8 columns (QID - primary key - int, CompID - foreign key - int, CollQ - varchar (500), UnAnc - varchar (500), AAnc - varchar (500), EAnc - varchar(500), ExperienceReq - bit, Selected - bit). I need to take the data from an Excel spreadsheet with the exact same column names and blast it into SQL. I have over 500 rows in the Excel files so I don't want to recreate this thing by hand. What I need to know is step by step how do I make this happen. I am new so please leave nothing out - I need to know things as trival as where to paste the code in SQL (e.g. new class or new query) - think trying to teach a monkey to drive a car. Thanks - any help is much appreciated.
Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Here is code I use to dump a table of data into a SQL Server table. It assumes there is no header row and the fields are in the same order. It also assumes the table starts in cell A1 on the active sheet.

Private Const TargetServerName As String = "Server1"
Private Const TargetDatabaseName As String = "Database1"
Private Const TableName As String = "Table1"

Public Sub WriteTable()
    Dim TargetDatabase As ADODB.Connection
    Dim TargetRecordset As New ADODB.Recordset
    Dim Record As Long
    Dim Field As Long
    ' Open target database connection
    Set TargetDatabase = New ADODB.Connection
    TargetDatabase.CursorLocation = adUseClient
    TargetDatabase.Open "Provider=SQLOLEDB;Data Source=" & TargetServerName & ";Initial Catalog=" & TargetDatabaseName & ";Integrated Security=SSPI"
    ' Delete existing records
    TargetDatabase.Execute "DELETE FROM dbo." & TableName
    ' Open target recordset
    TargetRecordset.Open TableName, TargetDatabase, adOpenDynamic, adLockPessimistic
    ' Copy records
    For Record = 1 To ActiveSheet.UsedRange.Rows.Count
        For Field = 1 To TargetRecordset.Fields.Count
            TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)
        Next Field
    Next Record
    ' Close recordset
    ' Close database

End Sub

jazzjAuthor Commented:

Thanks for the quick response. Where do I put this code in SQL. Do I paste it into a new query?  if not where and how? Also I am assuming I just need to have the Excel spreadsheet open but don't need to specifically reference the Excel file name - is that correct? Does the following look correct for my specific situation"

"Server 1" = RYN\sqlexpress
"Database1" = Collinfo.dbo
"Table1" = Collinfo_T

Thanks for the help.

zorvek (Kevin Jones)ConsultantCommented:
The code is VBA code and is placed in the Excel workbook, not SQL Server.

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To run a macro from Excel, select the menu command Tools->Macro->Macros or press ALT+F8. A dialog box appears listing all available macros. Find the desired macro and select it. Click the Run command button to start the macro.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

jazzjAuthor Commented:
That explains alot. Okay I ran it and it gives me the following error message.

Compile Error: User defined type not defined
it indicates the Dim TargetDatabase As ADODB.Connection line
jazzjAuthor Commented:

Scratch that. I selected the activeX library from the references and that seem to take care of the problem. Now the error I get is this:

Multiple Step Operation Generated Errors. Check each Status Value.
jazzjAuthor Commented:
This is the line where the error occurs:

TargetRecordset.Fields(Field - 1) = ActiveSheet.Cells(Record, Field)

I have checked my data types in SQL and make sure they match so not sure where to go from here.
jazzjAuthor Commented:

you're awesome. Your code worked perfectly. A couple things I needed to do in SQL to get it to work:

- delete my primary designations and reset the identity specification
- change some data types for text data
Was just curious if  anyone has sample code that does this same thing - but in DAO instead of ADO?  And from an Access 2007 database instead of Excel?   I'm just needing  to load a table or query that is in Access to a table in the SQL Server but with speed of the SQLBulkCopy class.   The conventional Access append query is just too slow.  And 'Pass-Through' Append queries will not work with Access queries or tables when your attempting to append data from them.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.