Click here to Skip to main content
13,860,571 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


6 bookmarked
Posted 21 Jan 2013
Licenced CPOL

Data Import from Excel File and Build an Automated Insert Script to Export into Excel File or to Export into SQL Server Table

, 21 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Data Import from Excel File and building an Automated Insert Script to Export into Excel File or to Export into SQL Server Table

Sometimes, it is necessary to import data from Excel file and insert into SQL Server table or build an automated script for inserting data into SQL Server table or build an automated script for inserting data and save into another Excel file to insert later. Today, I will discuss all the possibilities to solve this kind of problem. You need to follow one or more than one step of the following depending on your requirements.

Step 1: How to Import Data from Excel to SQL Server 2005

SQL Server OpenRowSet command makes the data transformation easily. You can use the following simple command to get the data from Import.xls file and stored into new table tmpTable1. You can perform an additional check whether the table is already existing or not. You have to specify the parameter for driver, file path and query into OpenRowSet().

('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\Import.xls;','SELECT * FROM [Sheet1$]')

Note: The following error could be raised if you do not configure the Surface Area Configuration below.

Error: SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Enable OPENROWSET and OPENDATASOURCE SUPPORT from the Surface Area Configuration to avoid error:

Enable OPENROWSET and OPENDATASOURCE SUPPORT  from the Surface Area Configuration

Step 2: How to Build a Data Insert Query Script Programmatically by Stored Procedure

The following stored procedure will return you the Insert Commands in the result Set. You can copy it from the results Grid and run from the Query Editor in SQL Server 2005 Management Studio.

i.e.  EXEC SP_DataInsertScript 'InsertTblName'
Create Procedure SP_DataInsertScript
@InsertTblName varchar(128)
exec SP_DataInsertScript 'InsertTblName'
DECLARE @TblName varchar(128) 
SELECT @TblName = 'tmpTable1' 
      create table #temp (id int identity (1,1), ColType int, ColName varchar(128))
      insert      #temp (ColType, ColName)
      select case when DATA_TYPE like '%char%' then 1 else 0 end ,
      from information_schema.columns
      where       TABLE_NAME = @TblName
      order by ORDINAL_POSITION
      if not exists (select * from #temp)
            raiserror('No columns found for table %s', 16,-1, @TblName)
declare     @id int ,
      @maxid int ,
      @cmd1 varchar(7000) ,
      @cmd2 varchar(7000) , 
      select      @id = 0 ,
            @maxid = max(id)
      from #temp
      select      @cmd1 = 'select '' insert ' + @InsertTblName + ' ( '
      select      @cmd2 = ' + '' select '' + '
      while @id < @maxid
            select @id = min(id) from #temp where id > @id
            select      @cmd1 = @cmd1 + ColName + ','
            from #temp
            where id = @id
            select @cmd2 =    @cmd2
                        + ' case when ' + ColName + ' is null '
                        +     ' then '''' '
                        +     ' else '
                        +      case when ColType = 1 then ''''''''' + ' + _
                               ColName + ' + ''''''''' else 'convert(varchar(20),' + ColName + ')' end
                        + ' end + '','' + '
            from #temp
            where id = @id          
      select @cmd1 = left(@cmd1,len(@cmd1)-1) + ' ) '' '
      select @cmd2 = left(@cmd2,len(@cmd2)-8) + ' from ' + @tblName
     exec (@cmd1 + @cmd2)
      drop table #temp

Step 3: How to Dump Insert Query into Another Excel File

If you want to dump resultset into Excel File, then you need to store the resultset in another temporary table and export from table into Excel file using bcp command. Let me explain how to do it:

DECLARE @QueryString varchar(1000)
CREATE TABLE testObjects1 ( QueryString varchar(1000) NOT NULL ) 
INSERT testObjects1 EXEC SP_DataInsertScript 'InsertTblName'

The above command will store the result set into testObjects1 Table. Now you have execute bcp command to export into Excel file.

Run the following command from the Query Window:

EXEC Master..xp_cmdshell 'bcp [dbo].testObjects1 out C:\Result.xls -U {UserName} 
-P {Pass} -S {ServerName} -T –c{or –n(binary)}'

Note: You have to Enable xp_cmdshell from the Surface Area Configuration to run the above command.

You have Enable xp_cmdshell from the Surface Area Configuration to run the above command


You can also run the following command from the command Prompt:

bcp [dbo].testObjects1 out C:\Result.xls -U {UserName} -P {Pass} 
-S {ServerName} -T –c{or –n(binary)}

The Result Excel file should be generated with Insert command here C:\Result.xls.



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


About the Author

MJ Ferdous
Architect Brain Station-23
Bangladesh Bangladesh
Ferdous has industry level experience with SharePoint and has done several presentations and workshops on SharePoint. He also worked as SharePoint Consultant (CREDEM Italy, Robi etc) and Trainer (BASIS, JAXARA IT, LEADS Co.). He is currently working as SharePoint Architect at BrainStation-23. He also worked as Technical Project Manager for Congral LLC for managing revolutionizing Patient Centric Healthcare applications at the same company since 2009.

He worked on many international projects during his professional life. The major projects are included below:
Internal Enterprise Portal for Robi, Dhaka
nVision Solution for nSales A/S, Denmark
Shared Care Plan for Congral LLC, USA
Internet Banking Portal for the Bank of CREDEM, Italy
Document Management (DMS) for the Bank of CREDEM, Italy
MES solution for Rockwell Automation, Italy
Tourism for Travel Curve Inc., USA
and so on...

He is the author of several technical articles with over 49 articles published on where he is the Technical Author for the site and author of as well. He is also founder of SharePoint Expert group.

Looking for a Offshore Development or partnership.............. in any development in Dot.Net & Sharepoint 2007,2010 Platform.

Search him in google by 'MJ Ferdous' to get all links, articles, profile etc

Contact him:


Production troubleshooting, maintainability and scalability
SharePoint 2007/2010
Dot.Net Application
Project Management
Document Management Solution

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
evan89719-Feb-13 2:28
groupevan89719-Feb-13 2:28 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02 | 2.8.190214.1 | Last Updated 21 Jan 2013
Article Copyright 2013 by MJ Ferdous
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid