Importing data from Excel through stored procedure

Question

I would like to enter my table name with the SP, so that it imports the data from excel sheet and loads onto database. But, receiving the following error. Can you please correct it. Thank you.

create proc Up_Export 
(
@Tablename as varchar(20) = null
)
AS
SET NOCOUNT ON
begin
INSERT INTO @Tablename --Receiving error over here, informs incorrect syntax near @tablename
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\..\..\..\packages\@Tablename.xls', @Tablename)
set nocount off
end

Solution

I see two things that aren't right.

First you do

INSERT INTO @Tablename 

You cannot use a variable at that place. Instead you should use dynamic sql like this

exec
('
INSERT INTO ' + @Tablename + ' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=C:\..\..\..\packages\@Tablename.xls'', ''select * from myTable'')
')

Note that you have to use double single quotes like '' inside of the string. "Escaping" the single quote is needed because if you didn't it would signal the end of the string.

The second thing which does not seem right is the second argument where where you put @Tablename

'Excel 8.0;Database=C:\..\..\..\packages\@Tablename.xls', @Tablename)

You should have something like the following as your second argument

'SELECT * FROM [Sheet2$]'

Where Sheet2 is the sheet in your excel

Try some variations and pay attention to the feedback the sql parser gives you in case of error. Good luck!