Click here to Skip to main content
15,918,404 members
Articles / DevOps

Excel Macro to Generate Database Insert Script

Rate me:
Please Sign up or sign in to vote.
3.96/5 (15 votes)
19 Jul 2019CPOL2 min read 36.5K   1.4K   25   12
An Excel macro that generates SQL insert script that can be executed against database directly


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.


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

    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.


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


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

Written By
India India
Anurag Gandhi is a Freelance Developer and Consultant, Architect, Blogger, Speaker, and Ex Microsoft Employee. He is passionate about programming.
He is extensively involved in Asp.Net Core, MVC/Web API, Node/Express, 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:
He can be contacted at

Comments and Discussions

QuestionThank you. Useful code which I modified slightly Pin
Member 135910751-Dec-19 8:36
Member 135910751-Dec-19 8:36 
AnswerRe: Thank you. Useful code which I modified slightly Pin
Anurag Gandhi14-Apr-21 20:29
professionalAnurag Gandhi14-Apr-21 20:29 
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
Member 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
Member 1449005218-Jul-19 10:58 
AnswerRe: Quotes in the string data Pin
Anurag Gandhi18-Jul-19 17:01
professionalAnurag Gandhi18-Jul-19 17:01 
Well, In SQL, Single Quote (') and "'" are not the same thing. But, if you want to store data that should be read only for web and it suits your purpose, you may modify your script as you have shown.

Member 14490052 wrote:
we need to go into the database and remove duplicate quotes.
When you replace (') with ('') in your text, only one quote will go to the database. So, you don't need to mess with it, i think.

In the tip, i covered only the Tip (Idea) with sample code on how to make your job easier. I understand that at times, people may have different requirement at different situations. I may create a full-fledged article with more scenarios covered in it in future.
I would be happy if the code helps you with a bit of modification.
Thanks for sharing your experience.
Life is a computer program and everyone is the programmer of his own life.

SuggestionWhat about datatypes? Pin
Daniel Leykauf16-Jul-19 11:20
Daniel 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
Member 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.