Is any error in my query?

baskaran chellasamy

14y1.5k

Hello friends
This is my store procedure
create procedure sp_excelforgeneralholidays(@filname nvarchar(max))
as
declare @datavar varchar(200)
declare @sql varchar(500)
set @datavar = 'Excel 8.0;DATABASE=' + @filname

set nocount on
begin
--create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
set @sql = INSERT INTO Genaral_holydays_details(Date,Day,Reason) SELECT Date,Day,Reason FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','+@datavar +;HDR=YES','SELECT Data,Day,Reason FROM [Sheet1$]')

exec (@sql)
end

and the excution part is
exec sp_excelforgeneralholidays'D:\baskaran\baskarbook1'

The error is incorrect syntax near INSERT

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

baskaran chellasamy14y ago
I want to import excel file dynamically to sql table General_holydays_details Excel file contain three column 1,Date,2.Day,3.Reason sql table contain 1.Holidayid identity(1,1),2.Date,3.Day,4.Reason 5.Createddatetime in sql table i just fill these 2,3,4 column from excel file. In the parameter of storedprocedure I sent filename with location. This is what i want to do
0
baskaran chellasamy14y ago
I want to import excel file dynamically to sql table General_holydays_details

Excel file contain three column 1,Date,2.Day,3.Reason
sql table contain 1.Holidayid identity(1,1),2.Date,3.Day,4.Reason 5.Createddatetime
in sql table i just fill these 2,3,4 column from excel file.

In the parameter of storedprocedure I sent filename with location.

This is what i want to do
0
Dorababu Meka14y ago
can you tell what you are trying to do with this query

set @sql = INSERT INTO Genaral_holydays_details(Date,Day,Reason) SELECT Date,Day,Reason FROM OPENROWSET('Microsoft.jet.OLEDB.4.0','+@datavar +;HDR=YES','SELECT Data,Day,Reason FROM [Sheet1$]')

May be this is not required i think so

INSERT INTO Genaral_holydays_details(Date,Day,Reason)
0