Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Create INSERT statements from Excel

By Arun Mallick,

Problem:
I am working on a project where I need to populate some configuration settings from an Excel file to the SQL server database so that on considering the values only my package will run. Here I want some easy method to populate those Excel data to my SQL server database. So that there will be no need to write Insert statements daily for each record. I want this task to be automated.

Solution:
Excel files includes a very good functionality called Macro execution. And the good thing is that we can write our own macro in VB and then we can execute it inside the Excel file. Here I too did the same thing, written one macro that will create INSERT statements for the current activated sheet.
To write Macro in Excel 2007:

  • Navigate to View tab in Excel

  • In View ribbon click on Macros Button

  • In the Macro Dialog box, give a valid name for a macro you want to create, and press Create button

  • In the code window, enter the script given below:

 

The code is self descriptive; in this I am fetching all the columns for the given active sheet at first. Then it will fetch the values for the corresponding columns. It will ask you for the range of data to be fetched like the starting Row no and the maximum Row no.
Testing:
Let's take the example of a small table named Student and the data for this is:

Name Roll Marks
Ram 101 89
Ashok 102 93
Kumar 103 78

In excel sheet it will look like this:

Here the starting row no is 2 and maximum row no is 4.
So after giving the inputs to the script, it will generate the below code:

insert into [Student] ( [Name] , [Roll] , [Marks] )
values( 'Ram', '101', '89');

insert into [Student] ( [Name] , [Roll] , [Marks] )
values( 'Ashok', '102', '93');

insert into [Student] ( [Name] , [Roll] , [Marks] )
values( 'Kumar', '103', '78');



Conclusion:
Creating a macro to generate Insert statements from the excel file is very handy and easy than importing data from excel to SQL server or by package creation. The code can be further customized to generate insert statements for all the sheets given in an excel file.

Total article views: 5121 | Views in the last 30 days: 5
 
Related Articles
FORUM
FORUM

Insert Local Excel data sheet into SQL(remote) Server

Insert Local Excel data sheet into SQL(remote) Server

FORUM

Run Excel Macro Using DTS

Problem scheduling Excel Macro if Macro debugs

FORUM

Delete Excel sheets in DTS

Delete Excel sheets in DTS

FORUM

How to read a data from an excel file having multiple sheets and insert into multiple tables tables

How to read a data from an excel file having multiple sheets and insert into multiple tables tables

Tags
excel    
insert    
sheet    
sql server    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones