Writing SQL Scripts

Writing and submitting SQL queries to a back-end database is the most important and useful feature of WinSQL Professional. Once a database connection is established, any type of SQL script may be written to extract data from the database.

 

You can highlight part of the script to run, or not highlight anything to run the complete script. If there is more than one result set, WinSQL Professional will display them one after another in different tabs.

 

Query Terminator

Multiple SQL queries can be separated by query terminators. By default, the value for query terminator is the word "GO". Consider the following example:

select * from  customers

GO

select * from  dbo.Employees

GO 

-- Joining two tables

select * 

from  dbo.Customers  INNER JOIN  dbo.Orders 

    ON  Customers.cust_id  =  Orders.cust_id

 

There queries are separated by the word GO, which appears on a separate line. Prior to submitting the SQL queries to the back-end, WinSQL will parse the script and extract multiple queries based on the value of query terminators. In the above example, each query is sent one after another. Some databases, such as MS SQL Server and Sybase can interpret multiple queries without any query terminator. In such cases, omitting the query terminator will produce the same result. On the other hand, database like Oracle and IBM DB2 require one query per statement and you will have to use a query terminator to separate each statement.

Configuring Query Terminator

To configure a value for Query Terminator, click Options under the Edit menu.

 

Special Comments

WinSQL supports following special comments that perform different tasks. These are single line comments and can appear before any script.Some comments do not accept any parameter while others do. Following section define different comments supported by WinSQL.

Result Tab Title

Comment Text:

wn_TabTitle

Parameter:

Any arbitrary title for result tab

Description:

Sets the title of a query tab in the result grid. This comment is very helpful when generating an EXE file based on a SELECT statement.

Example:

-- wn_TabTitle New Customers

select * from  customers

where  dateAdded  >  GetDate() - 30

 

Display Column Information for a Query

Comment Text:

wn_ShowColumnInfo

Parameter:

N/A

Description:

When present above a SELECT query, this comment will display the meta data for columns returned by that query. This comment have no effect on non-SELECT queries.

Example:

-- wn_ShowColumnInfo

select * from  Products

 

 

Named Query

Comment Text:

wn_NamedQuery

Parameter:

A name

Description:

Provides a convenient way for specifying a Named Query

Example:

-- wn_NamedQuery Month Summary for 2015

select * from  dbo.MonthRevenue   m

where  MonthName  like '2015%'

UNION

select 'Total YTD', sum(revenue)

from  MonthRevenue 

where  MonthName  like '2015%'

order by 1 desc

 

 

Save to a CSV file

Comment Text:

wn_SaveToCSV

Parameter:

Absolute path for output file

Description:

Provides a convenient way to save the results of a SELECT query to a CSV file.

Example:

-- wn_SaveToCSV C:\Temp\California.csv

SELECT  Customers.first_name,  Customers.last_name,  Employees.ssn,  Orders.o_date

FROM  ((dbo.Customers   Customers  INNER JOIN  dbo.Orders   Orders

          ON  Customers.cust_id  =  Orders.cust_id) INNER JOIN  dbo.Employees   Employees

          ON  Employees.emp_id  =  Orders.emp_id) 

WHERE (Customers.state  = 'CA')