How to Import Data from SQL

Introduction

There are many ways to get data into Q. This is the way to access data from an SQL database.

Requirements

• The database must be an SQL database.
• Any machine opening the Q project must be able to connect (over the network) to the database server. This may require your network administrators to reconfigure firewalls.
• You must know:
  • type of database (brand, e.g. Microsoft SQL Server)
  • server name
  • database name
  • user name
  • password
• The ADO.NET data provider appropriate to your brand of database must be installed on any machines that will open the project. Some data providers are included with Q (Microsoft SQL Server, ODBC, OLE DB), and you can install Oracle and others on your machine. Q supports Microsoft SQL Server and MySQL, but we can add others on request.
• You need to know how to to write SQL queries, or have someone who can help you.

Method

Q is able to read raw data from SQL databases. Add an SQL database to your project using File > Data Sets > Add to Project > From Database (SQL).

How to add an SQL database to an existing project

1. Open the existing project.
2. File > Data Sets > Add to Project > From Database (SQL).
3. Fill in the SQL Configuration (see the rest of this page for instructions).
4. Press OK.
5. Set up your project.

Setup

select date(Updated), count(*) as CountInQuota
  from Respondent
 where Updated > adddate(curdate(), -40) and Progress = 'Q'
 group by Date(Updated);

select C.Age
  from Customer C
 inner join #CUSTIDS I on C.CustomerID = I.ID;

How Q Interprets the Data

Each output field becomes a variable within Q. You can control the name of the variable by using as (see examples above). Q will automatically recognize date and date/time columns as Q dates, nvarchar/char as text, and everything else as numeric data. Some column types (e.g. binary data) cannot be used by Q, and will cause an error.

Suggestions

• A password is usually included in the connection string, so have your database administrator set up a database user account that is only able to read data, and only the data you need.
• Don't experiment with your SQL if you are connecting to an important database - have a database administrator help you.
• Only select the columns you need in Q, don't use select *. This will speed up your queries, reduce Q memory usage, and avoid problems that might occur with data Q cannot understand.
• While it is not possible to list the supported data types for every database vendor, Q will generally accept text, numeric and date/time data. e.g. VARCHAR, CHAR, NUMERIC, DATETIME.
• Use where clauses to fetch only the rows you need.
• Q provides no help for getting your SQL right. Therefore get your query working in a proper database tool first, and only then paste it into Q.

See Also

MySQL Worked Example

How to Upload Join IDs for SQL Data

How to Connect to a MySQL Database in Q

How to Set Up Files With No Metadata