Archives of the TeradataForum

Message Posted: Sat, 11 Nov 2006 @ 10:33:39 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

There are a couple of ways of boosting your productivity using SQL Assistant that I use frequently.

1) Did you know you can have multiple queries in your query, select the text of one and SQL Assistant will just submit the selected text? Thus you could have your drop table and create table queries all in the query window and submit them as needed simply by highlighting them. You need to enable this in the options "Query tab" "Submit only the highlighted, when selected".

2) Multi requests. After you create the table, it will exist, so next time, you can have the drop, and create in the query window, the drop will succeed because the table exists from last time.

3) Use the "create as" syntax to define the table as part of your query. Following is a simple example of create as.

     drop table mydbcinfo;

     create table mydbcinfo as (
         select *
         from dbc.dbcinfo
     )
     with data
     primary index (infokey);

I simply have those two queries in my query window modify the select as needed and hit F5, and voila, my table is recreated.

I'm not sure when "create as" syntax was introduced, maybe V2R5.1? It is also known as "copy table" syntax in the manual.

4) Use the history window - simply click on a query to retrieve it. It would be nice if you could submit from the history window without obliterating what is in the query window, maybe in a subsequent version.

As per some ones suggestion, delete is probably a better way to go if you aren't changing the table definition. But, if you are experimenting and, like me, typing and clicking adverse, simply have the above pair of queries and possibly a select * from your table sample 100 in the query window to view the results and just hit F5 and you will be right to go.

Finally, you don't need to load everything into a table which must be dropped, recreated (or altered if not dropped and created) to achieve what you want. SQL Assistant can be set up to restrict the number of rows returned to you. Again this is set in options "ANswerset" tab. So if it were me, I would dispense with the table all together, and just submit the query.

If you are developing a multi-step process then I would still work on the query as simply a select. Even if the query generates millions of rows, SQL Assistant can stop the retrieval of the answerset at the number of rows you specify. Once I am happy with the query, use the drop / create as combination, and then start on my next query as a select, once happy with it, convert it to a drop/ create as and so on.

I hope this helps.

Glenn Mc

	<Prev	Next>		<<First	<Prev	Next>	Last>>