{"id":5986,"date":"2012-10-25T14:34:37","date_gmt":"2012-10-25T14:34:37","guid":{"rendered":"https:\/\/www.snapsurveys.com\/support-snap11\/?page_id=5986"},"modified":"2018-10-04T13:01:59","modified_gmt":"2018-10-04T12:01:59","slug":"transferring-data-snap-survey-database","status":"publish","type":"page","link":"https:\/\/www.snapsurveys.com\/support-snap11\/worksheets\/transferring-data-snap-survey-database\/","title":{"rendered":"Transferring data between a Snap survey and a database"},"content":{"rendered":"

This worksheet describes how to link between a Snap survey and an SQL Server database, so you can use your existing database to seed your survey, or send updated data from your survey back to your database.<\/p>\n

You can set this up to happen automatically by using a Hot Link<\/strong>.<\/p>\n

This worksheet describes how to import data from an SQL server database. Exporting data is done in a similar manner.<\/p>\n

<\/a><\/p>\n

Background<\/h3>\n

You can link your Snap survey to a database. This allows you to seed the survey with data that is already in your database, or use email addresses in the database to invite respondents. You can also export data received from the survey back into the database.<\/p>\n

This worksheet shows you how to import data from the database to your survey. You can use a similar method to export data from your survey back to the database.<\/p>\n

To connect your survey to a database, you need to have a database field that contains a unique ID for each respondent that can be mapped to a respondent variable in the survey, so the data is put in the correct person’s record or response.<\/p>\n

Snap can automatically identify many database types. However, some databases have specific requirements, such as password protection. For these, you need to tell Snap precisely how to connect so it can build a data link. You use MicroSoft’s Data Link tool to do this.<\/p>\n

<\/a><\/p>\n

Summary of steps<\/h3>\n

Step 1: Setting up the Database Linkage Wizard<\/a>
\nStep 2: Creating the data link for the SQL database<\/a>
\nStep 3: Matching records between the survey and the database<\/a>
\nStep 4: Choosing what database fields to import into your survey<\/a>
\nSetting up a Hot Link to run the link automatically<\/a><\/p>\n

\n

<\/a><\/p>\n

Step 1: Setting up the Database Linkage Wizard<\/h3>\n
    \n
  1. Make sure that the database or spreadsheet you are importing from is closed.<\/li>\n
  2. Open the survey you wish to import the data into.<\/li>\n
  3. Click \"Data\u00a0to open the Data Entry window.<\/li>\n
  4. Select File | Database Link <\/strong>to display the Database Linkage dialog.\n
    \n

    \"Database<\/p>\n<\/div>\n<\/li>\n

  5. Click [New…] <\/strong>to create a new database link.<\/li>\n
  6. The Database Linkage Wizard<\/strong> dialog will be displayed.<\/li>\n
  7. Select the Import from Database radio button.\n
    \n

    \"Database<\/p>\n<\/div>\n<\/li>\n

  8. Click [Next>]<\/strong>.<\/li>\n
  9. The next step in the wizard asks you to select a database file or build a database link. Usually you will be able to click on the [Select Database File] <\/strong>button. If the database type you need is not listed as one of the defaults in the Select Database File dialog box then you can use the [Build Data Link] <\/strong>button to create the connection.
    \nThis worksheet describes how to build a link to an SQL database.<\/li>\n
  10. Click the [Build Data Link…] <\/strong>button.\n
    \n

    \"Database<\/p>\n<\/div>\n<\/li>\n<\/ol>\n<\/div>\n

    \n

    <\/a><\/p>\n

    Step 2: Creating the data link for the SQL database<\/h3>\n
      \n
    1. The Data Link Properties <\/strong>dialog is displayed.You can use the [Help]<\/strong> button on the dialog to display Microsoft’s help on this dialog.<\/li>\n
    2. Select the appropriate data type. For a link to an SQL Server database, select Microsoft OLE DB Provider for SQL Server<\/strong>.\n
      \n

      \"Data<\/p>\n<\/div>\n<\/li>\n

    3. Click [Next>]<\/strong>. The Data Link Properties <\/strong>dialog moves to the Connection <\/strong>tab.<\/li>\n
    4. Select the SQL server on your network from the dropdown list.<\/li>\n
    5. Select the radio button for the appropriate security type for the server.<\/li>\n
    6. Select the SQL Server database file from the drop-down list, or type the name of the file.\n
      \n

      \"Data<\/p>\n<\/div>\n<\/li>\n

    7. Click [Test Connection]<\/strong> to confirm that you can connect to your database.<\/li>\n
    8. Click [OK]<\/strong>. You will be returned to the Database Linkage Wizard<\/strong>.<\/li>\n<\/ol>\n<\/div>\n
      \n

      <\/a><\/p>\n

      Step 3: Matching records between the survey and the database<\/h3>\n
        \n
      1. Select the table or sheet in your database containing the data you want.\n
        \n

        \"DL<\/p>\n<\/div>\n

        Click [Next>]<\/strong>.<\/li>\n

      2. Select how you will import data to the survey. You will need a unique id for each person in the database that can be matched with a survey respondent (for example, a customer id or email address). This is called the link variable.
        \nJoin cases where the link variables match <\/strong>only imports data from the database if the id is already in the survey.
        \nJoin matching cases and append unmatched cases <\/strong>imports all data from the database. If the id is already in the survey, the appropriate data is added to that case. If it’s not, a new case is created.
        \nAppend unmatched cases <\/strong>creates new cases in the survey for the ids that are not already in the survey. It does not change the existing cases.
        \nAppend all cases <\/strong>creates new cases in the survey for every id in the database. It does not check if the ids are already in the survey.<\/li>\n
      3. Select the field in the database that contains the unique id from the Database field dropdown list.\n
        \n

        \"Database<\/p>\n<\/div>\n<\/li>\n

      4. Select the variable in the survey that contains the unique id from the Use Snap variable dropdown<\/strong> list. (You can also use the Snap case number as the unique number for each case.)<\/li>\n
      5. Click [Next>]<\/strong>.<\/li>\n<\/ol>\n<\/div>\n
        \n

        <\/a><\/p>\n

        Step 4: Choosing what database fields to import into your survey<\/h3>\n

        When you have chosen the database, database table, and matching variable, you then choose which fields in the database to import. The Database Linkage Wizard<\/strong> dialog shows a list of fields in the database to import from and is shown on the left, with variables in the current survey on the right.<\/p>\n

          \n
        1. Map the variables between the database and the survey. Snap works out the mapping where possible. To change a mapping, select a row in the right hand column, and click\u00a0\"List\u00a0to display a list of available variables in the survey. Select a variable to load data into, or select a blank if you do not wish to import the field.\n
          \n

          \"Data<\/p>\n<\/div>\n

          If you wish to import a field into your survey that does not already have a variable set up in your survey, select <Create Variable> from the dropdown list. The Name <\/strong>and Label<\/strong> automatically display the name of the database field. You can edit them if you wish. Set the response type and length to appropriate values and click [OK]<\/strong>.
          \n\"Create<\/p>\n

          If you wish to import data into a multi-response variable, you may need to set up which codes in the database match to which variable codes in the survey. When you select the multi-response variable in the right hand column, a code mapping area appears. Select the appropriate code or define a new one as you can with variables.
          \n\"Database<\/li>\n

        2. Click [Next]<\/strong> to display a summary of what the link does.<\/li>\n
        3. Check the details are correct, and enter a Name <\/strong>for the database link.<\/li>\n
        4. Click [Finish]<\/strong>. Snap shows the Database Linkage dialog including the new link.\n
          \"Database<\/p>\n
          <\/div>\n<\/div>\n<\/li>\n<\/ol>\n<\/div>\n