Scribd Logo
Upload

Welcome to Scribd!

  • 208 views

    How To Start SQL Assistant Using Batch Mode or Using Command Prompt?

    Uploaded by

    abreddy2003
    The document discusses how to start the SQL assistant using batch mode or the command prompt in Windows. It provides the command to run SQL assistant from the command line, specifying the connection, SQL file to run, and log file for the output. The command is "Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"". This allows SQL assistant to run SQL queries over time or for repetitive tasks from the command line or in batch files.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    How To Start SQL Assistant Using Batch Mode or Using Command Prompt?

    Uploaded by

    abreddy2003
    208 views9 pages
    The document discusses how to start the SQL assistant using batch mode or the command prompt in Windows. It provides the command to run SQL assistant from the command line, specifying the connection, SQL file to run, and log file for the output. The command is "Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"". This allows SQL assistant to run SQL queries over time or for repetitive tasks from the command line or in batch files.

    Original Description:

    Teradata doc

    Original Title

    Teradata Doc

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses how to start the SQL assistant using batch mode or the command prompt in Windows. It provides the command to run SQL assistant from the command line, specifying the connection, SQL file to run, and log file for the output. The command is "Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"". This allows SQL assistant to run SQL queries over time or for repetitive tasks from the command line or in batch files.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    208 views9 pages

    How To Start SQL Assistant Using Batch Mode or Using Command Prompt?

    Uploaded by

    abreddy2003
    The document discusses how to start the SQL assistant using batch mode or the command prompt in Windows. It provides the command to run SQL assistant from the command line, specifying the connection, SQL file to run, and log file for the output. The command is "Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"". This allows SQL assistant to run SQL queries over time or for repetitive tasks from the command line or in batch files.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 9
    At a glance
    Powered by AI
    SQL Assistant can be used to run queries against Teradata databases and export/import data. It has a graphical user interface and can be used in batch mode by calling it from the command line.

    SQL Assistant can be used in batch mode by calling it from the command line with parameters to specify the connection, input file containing queries, and output file for results.

    The steps are to login to the source database in SQL Assistant, run the export query, select the export location, and save the output file removing column headers.

    How to start SQL assistant using Batch Mode or using

    Command Prompt?
    How to start SQL assistant using Batch Mode or using Command Prompt.

    This is indeed interesting as there is not much documentation on how you can use SQL assistant via
    Batch mode or using Command prompt in Windows.

    For most of batch mode / Command prompt (interactive mode) we would use Bteq due to its flexibity and
    ease of use in either Windows or UNIX systems.

    There are some cases where you would like to use SQL assistant to run the set of queries over a period
    of time or for repetitive tasks. In such cases we could use following commands.

    Go to Windows start --> Run -->

    Enter following command

    Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"

    Explanation

    Sqla

    It is used to invoke sql assistant from its home directory.

    -c Test1

    -c is used to establish connection to Test1 server.

    Please make sure to setup ODBC connection string using ODBC administrator & enter IP address,
    Username & password.

    -f "c:\my queries\emplst.sql"

    The parameter –f is used to open a file in the given path directly on to Sql assistant.

    In above scenario, we are trying to open emplst.sql which is present in c:\my queries

    As a general rule I would enclose filenames in double quotes just in case they contain spaces or special
    characters.
    -e "c:\my queries\emplst_output.log"

    The parameter –e is used to export the results/ output into the given file.

    Sometimes we need to use –p ODBC or –p TD depending on version of TD sql assistant.

    I would suggest to use –p parameter whenever running the command as to identify the Data source as
    either ODBC or Teradata.net

    One could create a reusable batch file in windows and run this command with our sql file and record
    output in log file for reporting or whatever you want to do!!!

    Steps:

    1. Create file opensqla.bat

    2. Enter following command

    Start Sqla -c Test1 -f "c:\my queries\emplst.sql” -e "c:\my queries\emplst_output.log"

    Posted by Vinay at 4:51 AM 6 comments: Links to this post

    Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

    Labels: Teradata SQL Assistant

    Monday, August 23, 2010

    Export /Import using Teradata SQL assistant


    please Use this option for few reocrds only (say in 1000's) . For large data volumes use fastload (empty
    table loading) or Mload utilities

    1. Login to the Source Database using Teradata SQL Assistant. ( QA / PROD)

    2. Paste the Source SQL Query in the "Query" window.

    3. Select the following Menu Option "File=>Export Results"


    4. Go to "Tools=>Options" and select the Date Format as 'YYYY-MM-DD'.
    P.S: This option is used when a date column exists in the table.
    5. Run the Query (Press - F5) and Provide the Target Location where you want the output file to be
    saved:
    6. Open the Saved File (ex. Test_Export.txt) and remove the First Line from the File Which contains all
    the column Names.

    7. Login to the Target Database using Teradata SQL Assistant (Dev).

    8. Select the following Menu Option "File=>Import Data"


    9. Paste the SQL Insert Query in the "Query" window.
    ex. Insert into TBENW_RVLTN_REPL_LOC_SKU_FW values (?,?,?,?,?);

    NOTE: There should be a '?' as a Place-Holder for all the columns in your Source File.

    10. Run the Query (Press - F5).

    11. Turn off the Import Data mode and resume querying.

    Importing Excel Data into Teradata Table

    1. Make sure you have the columns in Excel in the same order as the table you created. Remove the
    header rows from the Excel file and Save the file as comma delimited file from Excel (CSV File).
    2. In SQL Assistant set the Delimiter as ","(comma) .
    ( Delimiter is available in Menu Tools , Options, Import/Export)
    3. Go to "Tools=>Options" and select the Date Format as 'YYYY-MM-DD'.
    P.S: This option is used when a date column exists in the table.

    4. From Teradata SQL Assistant, click 'File', then 'Import Data'.

    5. Run the command: insert into TABLENAME values (?,?,?....)


    You must supply a ? (question mark) for every column. So, for a table with 5 columns the values
    command would look like: values (?,?,?,?,?).

    6. Highlight the insert query and Press F5. This will prompt you for a file location. Select the appropriate
    file.

    7. This will load in the order in which they appear in your text file, which is why it is important to match the
    columns in Excel with the order of the columns in your table.

    8. Turn off the Import Data mode and resume querying.

    Posted by Vinay at 10:19 PM 11 comments: Links to this post

    Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest

    Labels: Teradata SQL Assistant


    Sunday, August 22, 2010

    Teradata SQL Assistant


    Teradata SQL Assistant

    DEFINITION

    Teradata SQL Assistant (TSA), as part of Teradata Tools and Utilities (TTU), is an ODBC-based client
    utility used to access and manipulate data on ODBC-compliant database servers

    TSA for Windows:

    Teradata SQL Assistant is an information discovery tool designed for Windows XP and Windows 2000.
    Teradata SQL Assistant retrieves data from any ODBC-compliant database server. The data can then be
    manipulated and stored on the desktop PC.

    OVERVIEW

    Teradata SQL Assistant for Microsoft Windows, originally called "Queryman" (before V. 6.0)or
    "QueryMan" (V. 6.0 and up), is also known as "SQLA" among programmers. It supports import / export
    tasks, but not the serious ones.
    With its user-friendly GUI, TSA for Windows is generally oriented to business users and casual
    data consumers. (Refer to BTEQ for comparison.) .

    The key features of Teradata SQL Assistant are:

    * Send queries to any ODBC database or the same query to many different databases;
    * Export data from the database to a file on a PC;
    * Create reports from any RDBMS that provides an ODBC interface;
    * Import data from a PC file directly to the database;
    * Use an import file to create many similar reports (query results or Answersets). For example, display
    the DDL (SQL) that was used to create a list of tables;
    * Create a historical record of the submitted SQL with timings and status information such as success
    or failure;
    * Use SQL syntax examples to tailor statements;
    * Use the Database Explorer Tree to easily view database objects;
    * Use a procedure builder that provides a list of valid statements for building the logic of a stored
    procedure;
    * Limit data returned to prevent runaway queries.

    You might also like