Click here to Skip to main content
14,303,709 members

Excel Macro to Generate Database Insert Script

Rate this:
3.71 (9 votes)
Please Sign up or sign in to vote.
3.71 (9 votes)
19 Jul 2019CPOL
An Excel macro that generates SQL insert script that can be executed against database directly

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.

Image 1

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".

Image 2

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Anurag Gandhi
Architect
India India
Anurag Gandhi is a Developer, Consultant, Architect, Blogger, Speaker and a Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Microsoft Azure/Cloud, web application hosting/architecture, Angular, AngularJs, design and development. His languages of choice are C#, Node/Express, JavaScript, Asp .NET MVC, Asp, C, C++. He is familiar with many other programming languages as well. He mostly works with MS Sql Server as the preferred database and has worked with Redis, MySQL, Oracle, MS Access, etc. also.
He is active in programming communities and loves to share the knowledge with others whenever he gets the time for it.
He is also a passionate chess player.
Linked in Profile: https://in.linkedin.com/in/anuraggandhi
He can be contacted at: soft.gandhi@gmail.com

Disclaimer: Any posts, article, content present in this website is my personal opinion based on best of my knowledge and experience. None of the content provided by me should be considered as official communication/opinion or my Employer.

Comments and Discussions

 
QuestionChanged script for Oracle Insertion and to skip hidden columns Pin
iamalik19-Jul-19 5:29
professionaliamalik19-Jul-19 5:29 
PraiseRe: Changed script for Oracle Insertion and to skip hidden columns Pin
Anurag Gandhi19-Jul-19 9:12
professionalAnurag Gandhi19-Jul-19 9:12 
QuestionQuotes in the string data Pin
Member 1449005218-Jul-19 6:39
memberMember 1449005218-Jul-19 6:39 
AnswerRe: Quotes in the string data Pin
Anurag Gandhi18-Jul-19 8:00
professionalAnurag Gandhi18-Jul-19 8:00 
GeneralRe: Quotes in the string data Pin
Member 1449005218-Jul-19 10:58
memberMember 1449005218-Jul-19 10:58 
AnswerRe: Quotes in the string data Pin
Anurag Gandhi18-Jul-19 17:01
professionalAnurag Gandhi18-Jul-19 17:01 
SuggestionWhat about datatypes? Pin
Daniel Leykauf16-Jul-19 11:20
memberDaniel Leykauf16-Jul-19 11:20 
AnswerRe: What about datatypes? Pin
Anurag Gandhi17-Jul-19 17:18
professionalAnurag Gandhi17-Jul-19 17:18 
QuestionGenerate Database Insert Script Pin
Member 1282942015-Jul-19 1:22
memberMember 1282942015-Jul-19 1:22 
AnswerRe: Generate Database Insert Script Pin
Anurag Gandhi15-Jul-19 5:54
professionalAnurag Gandhi15-Jul-19 5:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Tip/Trick
Posted 12 Jul 2019

Tagged as

Stats

7.4K views
345 downloads
19 bookmarked