Enclose a list of values into single quotes from CSV for T-SQL query for SQL Server database

This article demonstrates how to enclose a list of values into single quotes from CSV for the T-SQL query for the SQL Server database.

We often have a list of values to check the database to see if they exist in tables or get data corresponding to them. For example, if you were logged into your database, which stores all your stocks, you might want to check their latest closing price.

When working with strings, SQL Server databases use single quotes for string values. We need to add a single quote in the string for the searchable value.

For example, the below query specifies the where clause value in a single quote as ‘Chief Executive Officer'.

SELECT [NationalIDNumber]
      ,[LoginID]
      ,[JobTitle]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[Gender]
      ,[HireDate]
        FROM [AdventureWorks2019].[HumanResources].[Employee]
  where JobTitle='Chief Executive Officer'

Now, suppose you have a long list of data to search from the SQL table. You need to add single quotes in all values before executing the SQL query.

Is there any good way to do that? Yes, let’s explore.

Firstly, note-down all values in the Excel column we wish to use in the where clause.

In the 2nd column for the first row, paste the following formula and drag the formula for the remaining rows.

=CHAR(39) & A1 & CHAR(39) & CHAR(44)

It creates the data as shown below.

Copy the values from the 2nd column and paste them in the T-SQL query as below. Remove the last comma before running the select statement.

The query works fine and gives us results, as shown below.