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)
Thank this author by sharing:
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:
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:
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:
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.
Create Excel Sheet using SSIS
Insert Local Excel data sheet into SQL(remote) Server
Problem scheduling Excel Macro if Macro debugs
Delete Excel sheets in DTS
How to read a data from an excel file having multiple sheets and insert into multiple tables tables