/
Inserting an existing SQL statement

Inserting an existing SQL statement

Mar 03, 2020

Sometimes you may have a query that has already been perfected on another system. You can either:

  • Open the query file that has been provided or saved with a .dqy extension.

  • Paste a SQL statement into the SQL window on the Microsoft Query window

In this example, we will follow the process when someone from the Synergetic Management Systems team emails you a SQL statement. This Structured Query Language (SQL) statement is written in a language that database management systems can interpret. Synergetic uses the Microsoft SQL Server database management system.

In this example, we will use a simple SQL statement that will report and total all religion codes at your organisation:

Query

Query

SELECT vStudents.StudentCampus, vStudents.StudentReligionCode, Count (*)
FROM dbo.vStudents vStudents
WHERE (vStudents.CurrentSemesterOnlyFlag=1)
GROUP BY vStudents.StudentCampus, vStudents.StudentReligionCode
ORDER BY vStudents.StudentCampus, vStudents.StudentReligionCode

This statement selects campus, religion code and places a count field on your spreadsheet. The fields:

  • to be returned or calculated are placed directly after the SELECT clause

  • used to filter the records are placed directly after the WHERE clause

  • used to group the output are placed after the GROUP BY clause

  • used to sort the information are placed after the ORDER BY clause.

To paste in a SQL statement that has been sent to you by email:

  1. Start Microsoft Excel.

  2. Select:

    • Data > From Other Sources > From Microsoft Query (Microsoft Excel 2010 and Microsoft Excel 2013)


    • Data > Import External Data > New Database Query (Microsoft Excel 2007)
    • Data > Import External Data > New Database Query (Microsoft Excel 2003)
    • Data > Get External Data > Create New Query (Microsoft Excel 97).

    The Choose Data Source window is displayed.

  3. Select the Synergetic database.

  4. Clear the Use the Query Wizard to create/edit queries field.

     

  5. Click

    .
    The SQL Server Login window is displayed.

  6. Type in your username and password.

    Note: This is your usual Synergetic username and password.


  7. Click

    .
    The Add Tables window is displayed.

  8. Click

    .

  9. Click

    .
    The SQL window is displayed.

  10. Switch to the application with the SQL statement, such as an email message.

  11. Select the SQL statement text and press Ctrl + C.


    The SQL statement is copied to the clipboard.

  12. Switch back to the SQL window.

  13. Press Ctrl + V.
    The SQL statement is pasted into the SQL window.

     

  14. Click

    .
    The Microsoft Query window is updated with the new criteria and the records are displayed.

  15. You can:
    • select File > Save to save the query for later use


    • select File > Return Data to Microsoft Office Excel to return the data to a spreadsheet.