Using bulk operations using code with sql can be complex, time consuming and tedious for most of the programmers. I just used a stored procedure in one of my projects instead of traversing from cell to cell and save the value in the database, so I search the net and found this SQL Stored Procedure achieve this. I have just copied and paste the code as it is.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--drop procedure sp_excel_Convert_Excel_To_Table
--go
Create procedure [dbo].[sp_excel_Convert_Excel_To_Table]
(
@excel_full_file_name varchar(666)
,@convert_to_table_name varchar(50)
,@transfer_to_table bit=1
,@clear_existing_records_first bit=1
,@good int =null output
,@error_code int =null
,@error_description varchar(255) = null output
)
as
set nocount on
declare @command varchar(8000)
-- copy excel file under temp and change worksheet name
set @good=0
set @error_description = ''
set @error_code=0
declare @object int
,@hr int
,@src varchar(255)
exec @hr = master.dbo.sp_OACreate 'Excel.Application', @object out
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '1. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=1
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'DisplayAlerts', 'false'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '2. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=2
set @good=0
goto error
end
declare @workbook int
,@workbook_path_save_as varchar(666)
set @workbook_path_save_as = 'c:\test.xls'
exec @hr = master.dbo.sp_oaMethod @Object,'WorkBooks.Open',@workbook out,@excel_full_file_name
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '3. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=3
set @good=0
goto error
end
exec @hr = master.dbo.sp_OASetProperty @object, 'Workbooks(1).Worksheets(1).Name','excel_data'
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '4. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=4
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @workbook ,'SaveAs',null,@workbook_path_save_as
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '5. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=5
set @good=0
goto error
end
-- get full file name
exec @hr = master.dbo.sp_OAGetProperty @object, 'Workbooks(1).FullName',
@workbook_path_save_as output
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '6. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=6
set @good=0
goto error
end
exec @hr = master.dbo.sp_oaMethod @Object,'Application.Quit'--,@workbook out,@workbook_path
if @hr <> 0
begin
exec master.dbo.sp_OAGetErrorInfo @object, @src out, @error_description out
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
goto error
end
if @transfer_to_table = 1
begin
if @clear_existing_records_first=1
begin
set @command = 'delete from ' + @convert_to_table_name
exec (@command)
end
-- copy records from excel into table:
set @command = 'insert into ' + @convert_to_table_name +
' select * from ' +
' OpenRowSet(''MSDASQL'' '+
', ''Driver={Microsoft Excel Driver (*.xls)};'+
'DBQ=' + @workbook_path_save_as + ''''+
',''SELECT * FROM [excel_data$]'')'
print @command
exec (@command)
set @command = 'select * from ' + @convert_to_table_name
exec (@command)
end
error:
select @error_description as 'Error', @error_code as 'Error Code'
exec @hr = master.dbo.sp_OADestroy @workbook
if @hr <> 0
begin
set @error_description = '7. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=7
set @good=0
end
exec @hr = master.dbo.sp_OADestroy @object
if @hr <> 0
begin
set @error_description = '8. ' + isnull(@error_description,'N/A')
select hr=convert(varbinary(4),@hr), source=@src, description=@error_description
set @error_code=8
set @good=0
end