Excel macro to create database insertion script

Excel macro to create database insertion script

Introduction
For developers, adding/editing real or static data in a database once has always been a pain, as it requires writing scripts for every small set of text changes. For those who do not know SQL Server, it is easy to make these changes in Excel. For example, your business analyst or client has given you a set of settings/master data that should be directly copied into the database. In such cases, it is great if we generate the insert script directly through Excel and execute it directly in the database. This saves the time of script creation.

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

theme
Over the past 13 years of software development, I have felt the need many times to update any data I receive in Excel directly to SQL Server. Yes, there are different ways to do it. Like, direct import with column mapping, scripting, etc. But, I wish there was a direct but controlled way from Excel where the query is generated and I should have the option to change the query based on my need. Excel macro seems to be the closest solution I can think of.
This script uses UserForm with an editable text box to display and manage the generated script. It also contains a button to copy the generated script to the clipboard, which can be pasted directly into the SQL Query window.

Dear user, you are offered a download