Is it possible to use excel cell value - as a reference in T-SQL statement?

Question

I have table in SSMS (SQL Server Management Studio):

from which I need to delete data, based on the SurveyCode value.

Text part of the SurveyCode value CSS-2020-08- is stored in an Excel sheet, in B4 cell, file name Survey.xlsx:

And, for deleting / adding new data into my Survey table, I use the following code:

DELETE FROM [WH].[Fact].[Survey]
WHERE [WH].[Fact].[Survey].[SurveyCode] NOT LIKE '%2020-08%'

INSERT INTO
    [WH].Fact.[Survey]  (
          [SurveyCode]
         ,[SurveyDate]
         ,[Gender]
         ,[Age]
         ,[Questions]
         ,[Rating]
         ,[Score]
                       )
SELECT 
        staged.[SurveyCode]
       ,staged.[SurveyDate]
       ,staged.[Gender]
       ,staged.[Age]
       ,staged.[Question]
       ,staged.[Rating]
       ,staged.[Score]

FROM 
      [WH-SSIS].[WHCSS].[Staged_Survey] staged;

Is it possible - instead of using the SQL statement:

WHERE [WH].[Fact].[Survey].[SurveyCode] NOT LIKE '%2020-08%'

to reference CSS-2020-08- directly from the Excel spreadsheet - right inside the SQL statement?

Does this answer your question? [Code to read xlsx sheet into a table in a SQL Server database](https://stackoverflow.com/questions/24150739/code-to-read-xlsx-sheet-into-a-table-in-a-sql-server-database) — GSerg, Oct 04 '20 at 08:45
So [put that range in the query](https://stackoverflow.com/a/56284138/11683)? — GSerg, Oct 04 '20 at 09:08
@GSerg Openrowset works with FROM clause (with select from) But in my case I need something for WHERE clause... Openrowset won't work with WHERE I think — Hell-1931, Oct 04 '20 at 09:22
Does the account under which SQL Server runs have permissions to access that file? — GSerg, Oct 04 '20 at 10:12
Does the account under which SQL Server runs have permissions to access that file? yes — Hell-1931, Oct 04 '20 at 10:20
@GSerg Actually, I removed my updates! Openrowset () will insert all data from excel to SQL table. That's not what I need. I am looking for a solution - to insert data from one SQL db to another, WHERE SurveyCode from excel has a certain value — Hell-1931, Oct 04 '20 at 10:40
@Hell-1931 That isn't how tsql works. You need to reference a specific column in a specific row - regardless of where that "table" is located. You can't reference "B4" directly - you must get the value based on the primary or unique values that identify that row in your "table". This is beginning to sound like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — SMor, Oct 04 '20 at 11:23
Perhaps the better approach is to build and execute your query from excel using some vba to directly access a particular cell in a particular sheet (yes - you skipped the "sheet" part - an excel file can contain multiple sheets). Perhaps change your ETL process to delete the existing rows first? — SMor, Oct 04 '20 at 11:28
Openrowset does not insert anything. Openrowset lets you read Excel cells. You specify which cells you want to read. — GSerg, Oct 04 '20 at 13:20
@GSerg Sorry, my bad, I was kind of desperate yesterday Yes, it READS from excel; not inserts .... Below answer is explaining how to fight my issues — Hell-1931, Oct 04 '20 at 23:29

score 0 · Answer 1 · answered Oct 04 '20 at 11:57

I think

declare @parameter nvarchar(50)
SELECT parameter =f2
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=NO;Database=T:\temp\Survey.xlsx',
'select f2 from [sheet1$] where f1='''SurveyCode'''')

then you can use parameter in

WHERE [WH].[Fact].[Survey].[SurveyCode] NOT LIKE '%'+@parameter+'%'

score 0 · Answer 2 · answered Oct 04 '20 at 12:52

For reading the xlsx file, first of all you will need to have the right provider installed, you can check that by:

USE [master];
EXEC sys.sp_enum_oledb_providers;

You should see Microsoft.ACE.OLEDB.XX.0, where XX can be typically either 12 or 16. If you don't see it, then you will need to install the provider. Find it from MS here.

Then, you will need to have 'Ad Hoc Distributed Queries' enabled. You can check it by running:

EXEC sys.sp_configure 'Ad Hoc Distributed Queries';

If above command does not work and you are admin, then you will need to run below commands:

USE [master];
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

It is also good to check some more settings:

USE master;
EXEC dbo.sp_MSset_oledb_prop;

See allow_in_process and dynamic_parameters values for Microsoft.ACE.OLEDB.16.0 on this example. If they are 0, you will need to enable them as well:

USE master;
EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1;
EXEC dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1;

After all above checks, be sure the user which is running SSMS has read permissions for the xlsx file you want to read.

Finally, you should be able to run a query like below with no issues:

DECLARE @parameter NVARCHAR(50);
SELECT @parameter = F2
    FROM OPENROWSET(
        'Microsoft.ACE.OLEDB.16.0',
        'Excel 8.0;HDR=NO;Database=D:\temp\Survey.xlsx',
        'select * from [sheet1$]') AS xlsx
    WHERE xlsx.F1 = 'SurveyCode';

And you can use the @parameter value as part of your query:

...NOT LIKE '%'+@parameter+'%'

One other question - what is F2 and F1 in your code? – Hell-1931 Oct 04 '20 at 23:34 — Hell-1931, Oct 04 '20 at 23:34

Is it possible to use excel cell value - as a reference in T-SQL statement?

2 Answers2