Quick Query Trick SQL & Excel

Let’s say, someone gives you 100 values that they want you to look up in a database; however, you don’t want to make a table just to insert this values and join two tables. What can you do!!?

1.  Put the values into Excel

2. If in a column then copy the values, select an empty row, paste special, check transpose and then press ok. Now delete the column. If not skip this step. The goal is to have all the values you have in a single row and each value in a different cell.

3. Below your created Row, click on the first cell and type: “=Concatenate(“’”,A1,”’”)”

4. Press Enter, you should see the first value in single quotes.

5. Auto Fill Across the row so all your value are in single quotes.

6. Copy your formatted row, paste special in the row below and select Values only.

7. Delete the first two rows. Make sure you have only one row of value and it is the top most.

8. Save the file as a CSV. Close Excel.

9.  Open your file in Wordpad, copy your comma seperated values

10.  Go to your Select Statement for your SQL query:

Select * From tblMyTable Where ColumnName IN (<Paste Here>)

61 notes

  1. dibaji posted this