CODEWITHSUNDEEP
sqlexcel
user1679198
user1679198

Reputation: 63

Creating searchable table/array in SQL from excel column

I have some programming experience but am brand new to SQL.

Basically I have about 300 terms that I want to search for in a single search.

What is the best way to store those terms in a way that I can iterate through them in a query? They're currently in an excel column and I'd prefer not to have to manually write each one in

Upvotes: 0

Views: 226

Answers (1)

nitsram
nitsram

Reputation: 716

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

Will create a temporary table that you can run your queries against.

   SELECT * FROM
   SEARCHABLEDATABASE
   WHERE column_of interest IN
   ( SELECT search_terms FROM EXCEL_IMPORT )

Or use the SQL Server import wizard

OR Simply run the select queries directly against the sheet.

EDIT: These two queries will match entries in a column of database to terms from a excel spreadsheet, providing they are exactly the same) you could TRIM them both to prevent differences in whitespace causing issue.

Upvotes: 1

Related Questions