Excel Macro to Generate Database Insert Script

• Download sample-old - 20.3 KB
• Download sample - 25.9 KB

Introduction

For developers, one time adding/editing master or static data in database has always become a pain as it needs the script to be written for each small set of changes in text. It is easy to make those changes in Excel for those who don't know SQL Server. For example, your Business Analyst or client gave you a set of configurations/master data which directly needs to be copied to database. In such cases, it's great if we get the insert script generated directly via Excel and execute directly to the database. This saves a lot of time creating scripts.

This article assumes you have a basic idea of what is an Excel macro and how to run them.

Background

Over the past 13 years of software development, I felt the need many times where I wanted whatever data I get in Excel, should get updated directly to SQL Server. Yes, there are multiple ways of doing it. Like, direct import with column mapping, writing scripts, etc. But, I wished there should be a direct but controlled way from Excel where I get the query generated and I should have the liberty to modify the query based on my need. Excel macro seems to be the closest to the best solution that I could think of.

The script uses UserForm with editable textbox to show and manage the generated script. It also contains a button to copy the generated script to clipboard that can be pasted directly to the SQL Query window.

1st Version

This is the first version of the macro where the stored query were getting saved to "Sheet2".

Using the Code

The code below has certain assumptions:

• Table is present from the first cell of the sheet starting from top left (A1).
• Sheet2 should be present in Excel. Else, change the code to choose the sheet where you want the output to be written. (not required in new version)
• First Row is the column name of the database table for which insert script needs to generated.
• Name of the sheet should be TableName of database.
• Script generated will not be executed automatically. It needs to be executed manually after reviewing it.

Sub GetInsertSQL()
    Dim wsSrc As Worksheet: Set wsSrc = ActiveSheet
    Dim wsDest As Worksheet: Set wsDest = Worksheets("Sheet2")
    Dim LastRow As Long: LastRow = wsSrc.UsedRange.Find_
    ("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim LastCol As Long: LastCol = wsSrc.UsedRange.Find_
    ("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Dim i As Long, j As Long
    Dim strQuery As String

    wsDest.Cells.ClearContents
    
    strQuery = ""
    For j = 1 To LastCol
        strQuery = strQuery + "[" + CStr(wsSrc.Cells(1, j)) + "], "
    Next j
    strQuery = Left(strQuery, Len(strQuery) - 2)
    wsDest.Cells(1, 1) = "insert into [" + wsSrc.Name + "] (" + strQuery + ") "
    
    With wsDest
        For i = 2 To LastRow
            strQuery = ""
            For j = 1 To LastCol
              strQuery = strQuery + "'" + Replace(CStr(wsSrc.Cells(i, j).Text), "'", "''") + "', "
            Next j
            
            strQuery = Left(strQuery, Len(strQuery) - 2)
            .Cells(i, 1) = "(" + strQuery + "), "
        Next i
    End With
End Sub

Points of Interest

The above snippet generates only insert script. You may wish to generate update or delete command in a similar way. I will update the tip for generating update and delete statements as well in the near future.

In case you are getting a security warning saying, "Macros have been disabled", just click on "Enable Content" button to enable it.

History

• 2019-07-14: First version release
• 2019-07-20: User Form version release