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'.
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);