0

I'm trying to import the data from the Excel file to Table. Without using variable everything is working fine, however, when I try to declare var in the first place the execution ended up in error.

Script:

Declare @DateUsed NVARCHAR(30), @StringDatabase NVARCHAR(30);
SET @DateUsed = '2022-06-20.xlsx';
SET @StringDatabase = 'Database=C:\PATH -'

INSERT INTO Viator.dbo.Test SELECT *  
from  OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;'
 @StringDatabase + @DateUsed,
'SELECT * FROM [Table1$]'
);

If I'll replace the @StringDatabase and @DateUsed the query will finish successfully.

Error message:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '@StringDatabase'.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Wojciech
  • 27
  • 5
  • 1
    `OPENROWSET` expects literal values; you can't use expressions. If you need to use dynamic sources, you need to use dynamic SQL. – Thom A Jun 22 '22 at 11:29
  • Would you be able to show me if that is possible if I want to import the Excel into SSMS – Wojciech Jun 22 '22 at 11:37
  • Does this answer your question? [Using a Variable in OPENROWSET Query](https://stackoverflow.com/questions/13831472/using-a-variable-in-openrowset-query) – Thom A Jun 22 '22 at 11:39
  • Yes, that is the answer to my question, Thanks! – Wojciech Jun 22 '22 at 11:55

1 Answers1

1

Per @Larnu comment

The approach should be to define dynamic SQL based on one of the already provided solutions here

SET @DateUsed = '2022-06-20.xlsx';
SET @sql = 'INSERT INTO dbo.Test SELECT *  
from  OPENROWSET(
''Microsoft.ACE.OLEDB.12.0'',
''Excel 12.0;
Database=C:\PATH - ' + @DateUsed + ''',
''SELECT * FROM [Table1$]''
);'


exec(@sql);
Wojciech
  • 27
  • 5