2

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Venkat
  • 21
  • 1
  • 1
  • 2
  • Ironically, I have such a stored procedure. However, I have not yet documented it. Sometime soon, I'll probably put it on my blog. – Gordon Linoff May 21 '12 at 18:03
  • Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s May 21 '12 at 18:08
  • 2
    you can't `INSERT` into a varchar(20). you need to use dynamic sql. – KM. May 21 '12 at 18:11
  • 1
    Are you sure the Excel file is actually called "@Tablename.xls" and not just "Tablename.xls"? – Russell Fox May 21 '12 at 18:23

1 Answers1

3

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!

buckley
  • 13,690
  • 3
  • 53
  • 61