Using JAMS to create and manage SQL Tables

Follow

This article will explain how to configure JAMS Jobs that can perform various tasks within a SQL Table.

Sections

We'll use a few different statements in the examples below. They include:

  • The CREATE TABLE statement, used to create new tables in a database.
  • The INSERT INTO statement, used to insert information into a SQL Table's column.
  • The ALTER TABLE statement, used to add, delete, or modify columns in an existing table.
  • The ADD statement, used to add and drop various constraints on an existing table.
  • The UPDATE statement, used to update existing values in a SQL Table.
  • The SET argument, used with an update statement to define the column and value that will be set.
  • The WHERE clause, used with an update statement to specify which records should be updated.

Before You Start

Before you attempt to execute SQL Statements, ensure you are connected to the correct instance of SQL.

To connect to SQL, users can:

  • Set the ConnectionString Parameter on the SQLCommand Execution Method to define a default SQL instance for all Jobs
  • Create a Server-Specific SQLCommand Execution Method
  • Create and set the ConnectionString parameter on a Job or Folder to override the default SQL instance on the given Job or Folder level

Visit our Executing SQL Queries article for detailed instructions on connecting to SQL. 

 


 

Create a SQL Table

  1. Create a JAMS Job utilizing the SQLCommand Execution Method
    NOTE: If you created a custom Execution Method, you may use it instead of the SQLCommand Execution Method.
    Create_SQL_Table_Job.png
  2. Enter the SQL Code in the Job's Source to create the desired table. Edit the values as necessary for your use case.
    Create_SQL_Table_Job_Source.png

Source Code

  • Line 1 – Use Test (This is the Name of the Database Where the Table will be Created)
  • Line 2 - CREATE TABLE Customer (The Name of the Table that will be created)
  • Lines 3 through 7 -  The names of the Columns that are to be created and the datatype that they should be

Return to Top


 

Insert Information into an Existing Column within a SQL Database or Create a New Column

  1. Create a JAMS Job utilizing the SQLCommand Execution Method.
    NOTE: If you created a custom SQL Execution Method, you may use it instead of the SQLCommand Execution Method.
    Update_SQL_Table_Job.png
  2. Enter the SQL Code into the Job's Source to insert information into the desired existing SQL Table. Edit the code values as necessary for your use case.
    Update_SQL_Table_Source.png

Source Code

  • Line 1 – Use Test (This is the Name of the Database Where information will to be inserted into a specific table)
  • Line 2 – Insert INTO Customer  (The Name of the Table, in this case ‘Customer”, that will be inserting the data into), (CustomerID, LastName, Firstname, Address, City) – The Columns the information will be inserted into
  • Line 3 - VALUES ('45', 'Smith', 'John', '3 Any Street', 'Anytown'); - The Values that will be inserted into each Column in Line 2

Return to Top


 

Adding a new Column to an existing Table

  1. Create a JAMS Job utilizing the SQLCommand Execution Method.
    NOTE: If you created a custom SQL Execution Method, you may use it instead of the SQLCommand Execution Method.
    Add_SQL_Column_Job.png
  2. Alter the SQL Code below to fit your given use case, then enter it into the Job Source in JAMS.
    Add_SQL_Column_Job_Source.png

Source Code

  • Line 1 – Use Test (This is the Name of the Database Where a new column will be inserted into a specific table)
  • Line 2 – Alter Table Customer (The name of the Table we will be adding a Column INTO. In this case “Customer”)
  • Line 3 – Add PhoneNumber int; (The name of the new Column and the Datatype for that Column)

Return to Top


 

 

Editing values within a SQL Table

In this last example, we'll use JAMS Parameters to create a Job that will allow us to update the values in a SQL Column without having to edit the SQL Code itself. In advanced cases, this method could be used to pass information from an external source, such as an email or web application, to set the parameter value. 

  1. Create a JAMS Job utilizing the SQLCommand Execution Method.
    NOTE: If there is a custom SQL Execution Method, it may be used instead of the SQLCommand Execution Method.
    Self_Service_Update_SQL_Values.png
  2. Enter the SQL Code into the Job Source.
    /*Enter the name of the database where the table resides.*/ 
    /*This is written as a JAMS Parameter so it may be set at runtime*/
    <<DatabaseName>>
    /*Sets the Table that will be updated.*/
    /*This is written as a JAMS Parameter so it may be set at runtime*/
    UPDATE <<TableName>>
    /*Specifies the column and values to update from and update to.*/ SET <<ColumnName>> = '<<UpdateTo>>' WHERE <<ColumnName>> = '<<UpdateFrom>>'
    Self_Service_Update_SQL_Value_Source.png
  3. Set the applicable parameters on the Job.
    Self_Service_Update_SQL_Values_Parameters.png
  4. Now, when the Job is submitted manually, users will see the available parameters and can set them at runtime.
    Self_Service_Update_SQL_Values_Submit.png

In the example above, the Customer table had some bad data in the City column, where Anytown was used in place of New York City. By entering parameters, the end user would have scheduled a Job to update all city values of "Anytown" to "New York City". 

Return to Top

Have more questions? Submit a request

Comments