How-to: Reuse favorite queries easier than ever
Have you collected, or just wish you had collected a bunch of useful queries that can be used again and again or do you plan starting to do it? Then here is a suggestion:
You can easily save these queries in a SQLMerger project. That way you always have them at hand and can easily get a hold of them again later. This also helps you reach your query's data quickly, with few clicks.
During project development once in a while you need to query the database. Some queries helps one to gain insight into a part of a configuration, other for example are queries for finding incomplete things. It can be much else.
Saving queries in a SQLMerger project is an alternative for storing queries in different locations, or to not get them saved at all. Why not save them all in a SQLMerger project where it is super quick to get their data content shown again and again?
Here is how you can manage this:
Do:
1. Create a SQLMerger project.
2. Enter the connection settings under the "Database" tab, for the Target database.
3. Save the project for example in your project's project folder with a descriptive name for example: "CommonQueries.SQP"
4. Create for each new query, a merge item.
As an example, we will inquire what "EventTypes" are not tested in our application.
Note: if you want your table to try with, you can create it with:
CREATE
TABLE
EventType (
Id INT
PRIMARY
KEY
,
EventName VARCHAR
(
50),
EventTested BIT
DEFAULT
(
0)
NOT
NULL
);
INSERT
INTO
EventType (
Id,
EventName,
EventTested)
VALUES
(
1 /* Id */
,
'A'
/* EventName */
,
1 /* EventTested */
);
INSERT
INTO
EventType (
Id,
EventName,
EventTested)
VALUES
(
2 /* Id */
,
'B'
/* EventName */
,
0 /* EventTested */
);
INSERT
INTO
EventType (
Id,
EventName,
EventTested)
VALUES
(
3 /* Id */
,
'C'
/* EventName */
,
0 /* EventTested */
);
Enter the name "Untested Events" in the "Merge Item Name" field in the project.
|
|
This is the project with the first merge item, with the name "Untested Events" entered
|
5. Enter your query in "Target SQL Query" edit:
SELECT
*
FROM
EventType
WHERE
EventTested =
0 AND
NOT
Id IN
(
5,
6)
ORDER
BY
Id
|
|
Here you can see the query keyed in
|
6. Click the [Open Target] button.
|
|
The data returned by the query.
|
Next Query
7. Choose the option ”Add New Merge Item” from the "Item" menu.
This time, we will ask how many events have been generated by a given type, so now we need to create a merge item more, with yet another query.
Note: if you want your table to try with, you can create it with:
CREATE
TABLE
Event
(
Id INT
PRIMARY
KEY
,
EventTypeId INT
REFERENCES
EventType(
Id)
NOT
NULL
);
INSERT
INTO
Event
(
Id,
EventTypeId)
VALUES
(
1 /* Id */
,
1 /* EventTypeId */
);
INSERT
INTO
Event
(
Id,
EventTypeId)
VALUES
(
2 /* Id */
,
1 /* EventTypeId */
);
INSERT
INTO
Event
(
Id,
EventTypeId)
VALUES
(
3 /* Id */
,
2 /* EventTypeId */
);
INSERT
INTO
Event
(
Id,
EventTypeId)
VALUES
(
4 ,
2 );
INSERT INTO
Event
(
Id,
EventTypeId)
VALUES
(
5 /* Id */
,
2 /* EventTypeId */
);
INSERT
INTO
Event
(
Id,
EventTypeId)
VALUES
(
6 /* Id */
,
2 /* EventTypeId */
);
INSERT
INTO
Event
(
Id,
EventTypeId)
(
7 /* Id */
,
2 /* EventTypeId */
);
The query we will execute should be:
SELECT
EventTypeId,
Count
(*)
AS
NumberOfEvents
FROM
Event
GROUP
BY
EventTypeId
8. Enter a good name and click [OK]. We will use "Eventtype Usage Count"
|
|
Giving the new merge item a name.
|
9. Open the new merge item.
|
|
Data for "Eventtype Usage Count"
|
10. Save the project and quit the program
Daily Use
Now your queries at your fingertips, try this:
11. In Windows, browse your way to the project you saved.
You can also create a shortcut to the project, in your Windows Start menu.
12. Double click the SQLMerger project.
That will start up SQLMerger.
13. Place the cursor in the Quick Locator and enter e.g. the first letter of the name you gave your Merge Item. "E"
It will make the quick locator find your query. Press the Return key. This now scrolls the project to the merge item found.
|
|
The new merge item
|
14. Click on the [Open Target] button.
Your data is (already :-) ) ready.
|
|
|
Query data displayed
|
Back to Overview...
|
