In both examples you delete table in which later you're trying to insert data.
INSERT[
^] statement is wrong.
Alter PROC SP_Excel
@ExcelName VARCHAR(255),
@SheetName VARCHAR(50)
AS
BEGIN
DECLARE @fExt VARCHAR(3)
SET @fExt = RIGHT(@ExcelName, 4)
IF (@fExt = '.xls')
BEGIN
INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\Shared\EXCEL\' + @ExcelName + '', 'SELECT * FROM [' + @SheetName +'$]')
ELSE
INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Shared\EXCEL\' + @ExcelName + '','SELECT * FROM [' + @SheetName + '$]')
END
END
Note:
1) Not tested!
2) Column mapping must be the same, so, better way is to define columns:
INSERT INTO Table1 (Col1, Col2, Col3... ColN)
SELECT Col1, Col2, Col3... ColN
FROM OPENROWSET (...) AS T
[EDIT #1]
I tested this:
DECLARE @ExcelName VARCHAR(255)
DECLARE @SheetName VARCHAR(50)
DECLARE @sql VARCHAR(MAX)
SET @ExcelName = 'VeryVeryVeryLongFileName.xls'
SET @SheetName = 'Sheet1'
SET @sql = N'INSERT INTO Table1 SELECT * FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database=D:\Shared\EXCEL\' + @ExcelName + ',''SELECT * FROM [' + @SheetName + '$]'')'
PRINT @sql
and it should works fine, because it shows result as:
INSERT INTO Table1 SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=D:\Shared\EXCEL\VeryVeryVeryLongFileName.xls,'SELECT * FROM [Sheet1$]')
Please, change the code as is shown above.
[/EDIT]