Database Jobs › Defining SQL Jobs › Examples: Running SQL Statements Against Microsoft SQL Server Database Tables

Examples: Running SQL Statements Against Microsoft SQL Server Database Tables

This topic contains examples of SQL jobs that run SQL statements against Microsoft SQL Server database tables.

Example: Add a Row to a Microsoft SQL Server Database Table

Suppose that you want a job to add a row for a new store to the stores table.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SQL 'INSERT INTO stores(stor_id, stor_name, stor_address, +
  city, state, zip) VALUES(''6523'', ''Chapters'', +
  ''6523 Main St.'', ''San Diego'', ''CA'', ''93223'')'

Example: Delete a Row from a Microsoft SQL Server Database Table

Suppose that you want a job to delete the row for store ID 6523 from the stores table.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SQL 'DELETE FROM stores WHERE stor_id=''6523'''

Example: Update a Row in a Microsoft SQL Server Database Table

Suppose that you want a job to update the row in the sales table that matches order number 6871 and change the values for the order date and quantity.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SQL 'UPDATE sales SET ord_date=''6/15/2006'', qty=10 WHERE +
  ord_num=''6871'''

Example: Return Data from a Microsoft SQL Server Database Table that Matches a Condition

Suppose that you want a job to query the sales table for orders that have a quantity greater than 20. The order numbers that match the query appear in the output file ordnum.txt.

AGENT DB_AGENT
USER sa
DB_URL 'jdbc:sqlserver://myhost:1433;DatabaseName=pubs'
SQL 'SELECT ORD_NUM FROM SALES WHERE QTY > 20'
JOB_CRITERIA ORD_NUM=A2976
OUTPUT_FILE C:\sales\ordnum.txt

The ordnum.txt file contains the following order numbers:

A2976
QA7442.3
P2121
N914014
P3087a
P3087a
X999
P723
QA879.1

Because the query returns an order number that matches the job criteria A2976, the job completes.

Suppose we change the job criteria statement in the above example to the following:

JOB_CRITERIA .*B[0-9]

In this case, the query would still return the same order numbers, but the job would fail because it would not find a matching order number containing the letter B and followed by a number.