Uploading Join IDs for SQL Data

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.

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.

		Example (MySQL)	Example (SQL Server)
Before first ID	SQL text to create the temporary table and begin inserting the first record. Q will follow this with the first (quoted if necessary) ID.	drop temporary table if exists {0}; create temporary table {0}( ID {1} not null, primary key(ID)); insert into {0}(ID) values(	if object_id('tempdb..{0}') is not null drop table {0}; create table {0}( ID {1} not null, primary key(ID)); insert into {0}(ID) select
Between IDs	SQL text between each ID to be uploaded.	), (	union all select
After last ID	SQL text that completes the inserts.	);	;

Substitutions

You need not use these substitutions when manually configuring the join.

          They are documented here mainly to help you make sense of the examples.

{0}	The name of the table.
{1}	The data type for the ID column. This will be a numeric or text data type depending on whether the ID variable you are uploading is numeric or text, and are chosen to match your database type. Unknown databases will use nvarchar(2*w) and real. If these are inappropriate then don't use this substitution.

See also

SQL Data