January 14, 2011 at 12:39 am
I am struggling with this and could not figure out. I need to import an excelfile as an variable.
The code is
----------
Create Proc bulkinsertfromexcel
as
Begin
Declare @sessionID nvarchar(MAX)
Declare @docpath nvarchar(max)
Declare @jobcreator nvarchar(max)
Declare @SQL nvarchar(MAX)
DECLARE @filename varchar(100)
Set @docpath = 'E:\Workflow\SPP\Attachments\TR\ExcelUpload\''+@filename'
Set @SQL = INSERT INTO [Tactical.Claim] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT A.[JObCreator], A.[ModelCode], A.[ChassisNumber], A.[CommisionNumber],
A.[AG], A.[AVME], A.[Brand], A.[Remarks]
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;;Database=''+@docpath+', 'select * from [Sheet1$]') AS A;
PRINT @SQL
EXEC(@SQL)
End
---------------
The error is
-----------
Line 11, Incorrect syntax near the keyword 'INSERT'
------------
Any help to figure out this issue and run the sp successfully is highly appreciated
January 14, 2011 at 3:38 am
Shouldn't the whole Set @SQL = be into quotes?
Set @SQL = 'Insert and so on'
January 14, 2011 at 4:32 am
Actually I just made my query little simple to test if its working. but still i am getting the errors below
---------code------------
USE AVME
Declare @SQL nvarchar(MAX)
Declare @docpath nvarchar(MAX)
Set @docpath = N'E:\Workflow\SPP\Attachments\ModelCodes\claim.xls'
Set @SQL = 'INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT *
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database='''+@docpath+''';HDR=YES'',
''select * from [Sheet1$]'')'
PRINT @SQL
EXEC(@SQL)
-----result + error----------
INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT *
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;;Database='E:\Workflow\SPP\Attachments\ModelCodes\claim.xls';HDR=YES',
'select * from [Sheet1$]')
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'E'.
January 14, 2011 at 7:40 am
Remove the single quotes around E:\...\claim.xls
Far away is close at hand in the images of elsewhere.
Anon.
January 14, 2011 at 11:12 am
The single quote is shown in the output, that is where I am stuck and do not know how to get rid of that single quote (database ='E:\......)
January 14, 2011 at 4:49 pm
Try this.
Declare @SQL nvarchar(MAX)
Declare @docpath nvarchar(MAX)
Set @docpath = N'E:\Workflow\SPP\Attachments\ModelCodes\claim.xls'
Set @SQL = 'INSERT INTO [Tactical.Claim_LineItems_BULK_Audi] ([JObCreator], [ModelCode],
[ChassisNo], [CommissionNo], [AGSupport],[AVMESupport], [Brand], [Remark])
SELECT *
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database=' +@docpath+ ';HDR=YES'',
''select * from [Sheet1$]'')'
PRINT @SQL
EXEC(@SQL)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply