How to Upload Join IDs for SQL Data

Introduction

Sometimes you want your SQL data to look up only data that matches the cases in another data file.

For example, you might have a raw data file that includes a CustomerID variable, and you would like to look up each corresponding customer's age in a database. Unfortunately, the database contains millions of customers, and you only need the records for the few thousand in your survey. In this case, Q could download the age of all customers and then discard those that don't match, but this might be very slow, or Q might run out of memory.

Far better is for Q to upload just the list of cases required into a temporary table in the database. Your SQL query can join with this temporary table to only return the rows needed.

Requirements

• SQL Database 
• Lookup data that match cases in another file

Method

Specifying Joins

To upload IDs from one or more variables, follow the steps below for each.

1. Click on the Advanced... button.
2. Click on New....
3. Select the name of the file (or other data) that you want to use as the source of IDs for your join.
4. Select the name of the variable to upload.
5. Enter a name to use for the temporary table that will be created to hold the IDs.
6. Modify your SQL to join to the table you specified.

Notes

• All IDs from the joined data file are uploaded, including any that you have manually deleted in the Data tab.
• MySQL: You will need to create a temp database and give the database user permission to create tables there. e.g.

  grant insert, select, create temporary tables on temp.* to username;

• Microsoft SQL Server: Ensure table names start with the # character, so they are temporary tables.

Manually Configuring for Upload of Join IDs

Unfortunately, every brand of database differs in how to create temporary tables, and in the mechanism to efficiently populate those temporary tables. Q, therefore, allows you to dictate the SQL code to be used to upload the IDs to match on. However, you only need to configure the below if Q tells you that your database is not already supported, or if you want to do something differently.

See Also

How to Import Data from SQL

MySQL Worked Example

How to Connect to a MySQL Database in Q