IMPORTING AND EXPORTING DATA FROM SQL SERVER TO EXCEL FILE:

Today I had the requirement to Read the Excel File and Extract the Data from SQL Server based on the Excel File and then Export the Data to the Different Excel Files.
So I break down the Task into Three Aspects:
Importing the Data From Excel File: OPENDATASOURCE:
Invoking the Command Prompt :XP_CMDSHELL
Using the Bcp.exe to Export the Data to the Excel File: BCP.EXE

Before running those commands,the SQL server should be enabled to run xp_cmdshell and OpenDataSource Commands.Those are server wide settings.

1.Reading the Data From Excel:
select * into temp from opendatasource
('microsoft.jet.oledb.4.0','data source= "G:Test.xls";extended properties=excel 5.0')...sheet1$
This reads the Data From the Excel File: G:\Test.xls
The sheetName :sheet1$

2.Using the XP_CMDSHELL:
I use xp_cmdshell with the BCP Option.
This invokes the command Prompt.

3.Using the BCP.EXE:
USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TestSp_ExcelFile]
(@file varchar(255))
as
begin
set nocount on
if exists(select * from sysobjects where id=object_id('temp')and xtype='u')
begin
print cast('dropping the table'as varchar(255))
drop table temp
end
declare @filename varchar(255)
declare @string nvarchar(2000)
declare @id int
declare @commandstring varchar(2000)
declare @vtargetfilename varchar(255)
declare @targetfilename varchar(255)
declare @targetpath varchar(255)
declare @position int
set @filename=@file
set @string='('+'''microsoft.jet.oledb.4.0'''+','+''+'''data
source= '+'"'+@filename+'"'+';'+'extended properties=excel 5.0'')'+'...sheet1
set @string='select * into temp from opendatasource '+ @string
execute sp_executesql @string
Print @string
declare c scroll cursor for
select columnname from temp
open c
fetch absolute 1 from c into @id
while @@fetch_status=0
begin
set @commandstring='select * from Adventureworks.Person.Address where AddressID ='+ cast(@id as varchar(20))
set @vtargetfilename=@filename
set @position=(datalength(@vtargetfilename)-charindex ('\',reverse(@vtargetfilename),0))
select @targetpath=left(@vtargetfilename,@position+1)
set @targetfilename=@targetpath +cast(@id as varchar(20))+'.xls'
set @commandstring = 'bcp.exe '+'"'+@commandstring+'"'+' queryout '+'"'+ @targetfilename +'"'+'-T -c'
print @commandstring
execute xp_cmdshell @commandstring
fetch next from c into @id
end
close c
deallocate c
end

I am using the dynamic SQL to create the Excel File for Each Row available in the Source Excel File.