Teradata Community

Payload examples for Loader

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Options
browserTDA
browserTDA
Teradata Employee
‎08-27-2010 03:56 PM
‎08-27-2010 03:56 PM

Payload examples for Loader

This section provides several payload and expected-result examples for Loader.

Example 1

Variable Payload Format - Insert

Format:Variable
Delimiter:(,)
DML type:Insert
Columns for INSERT:f1, f2, f3
Payload:111,222,333
Result:
insert of 1 record with
f1=111 f2=222 f3=333

Example 2

Variable Payload Format - Update

Format:Variable
Delimiter:(,)
DML type:Update
Columns for UPDATE:f1, f2, f3
Columns in WHERE clause:f1, f2, f3
Payload:1xx,2xx,3xx,111,222,333
Result:
update of all records
where f1=111 and f2=222 and f3=333

Example 3

Variable Payload Format - Upsert

Format:Variable
Delimiter:(,)
DML type:Upsert
Columns for UPDATE:f2
Columns in WHERE clause:f1, f3
Columns for INSERT:f1, f2, f3
Payload:2xx,111,333,111,222,333
Result:
update of all records 
setting f2=2xx
where f1=111 and f3=333

if there's no match with the where condition then insert 1 record with
f1=111 f2=222 f3=333

Example 4

Payload Format - Delete

Format:Variable
Delimiter:(,)
DML type:Delete
Columns in WHERE clause:f1, f2, f3
Payload:111,222,333
Result:
deletion of all records
where f1=111 and f2=222 and f3=333

Example 5

Fixed Payload Format - Insert

Format:Fixed
DML type:Insert
Columns for INSERT:f1, f2, f3
Payload:111111111122222222223333333333
Result:
insert of 1 record with
f1=1111111111 f2=2222222222 f3=3333333333

Example 6

Fixed Payload Format - Update

Format:Fixed
DML type:Update
Columns for UPDATE:f1, f2, f3
Columns in WHERE clause:f1, f2, f3
Payload:1xxxxxxxxx2xxxxxxxxx3xxxxxxxxx111111111122222222223333333333
Result:
update of all records to
(f1=1xxxxxxxxx f2=2xxxxxxxxx f3=3xxxxxxxxx)
where (f1=1111111111 f2=2222222222 f3=3333333333)

Example 7

Fixed Payload Format - Upsert

Format:Fixed
DML type:Upsert
Columns for UPDATE:f2
Columns in WHERE clause:f1, f3
Columns for INSERT:f1, f2, f3
Payload:"2xxxxxxxxx                              2222222222          "
Result:
update of all records 
setting f2=2xxxxxxxxx
where f1=empty string and f3=empty string

if there's no match with the where condition then insert 1 record with
f1=10 spaces and f2=2222222222 and f3=10 spaces

Example 8

Fixed Payload Format - Delete

Format:Fixed
DML type:Delete
Columns in WHERE clause:f1, f2, f3
Payload:111111111122222222223333333333
Result:
deletion of all records
where f1=111 and f2=222 and f3=333

Example 9

XML Payload Format - Insert

Format:XML
DML type:Insert
Columns for INSERT:f1, f3
Payload:
<?xml version="1.0" encoding="utf-8"?>
<Message type="TERAJMSA">
  <records>
    <row>
      <f1>111</f1>
      <f2>222</f2>
      <f3>333</f3>
    </row>
    <row>
      <f1>111</f1>
      <f2>222</f2>
      <f3>333</f3>
    </row>
  </records>
</Message>

Result:
insert of 2 records with
f1=111 and f2=null and f3=333

Example 10

XML Payload Format  - Update

Format:XML
DML type:Update
Columns for UPDATE:f1, f2, f3
Columns in WHERE clause:f1, f2, f3
Payload:
<?xml version="1.0" encoding="utf-8"?>
<Message type="TERAJMSA">
  <records>
    <row>
      <update>
        <f1>1xx</f1>
        <f2>2xx</f2>
        <f3>3xx</f3>
      </update>
      <where>
        <f1>111</f1>
        <f2>222</f2>
        <f3>333</f3>
      </where>
    </row>
  </records>
</Message>

Result:
update of all records
setting f1=1xx f2=2xx f3=3xx
where f1=111 and f2=222 and f3=333

Example 11

XML Payload Format - Upsert

Format:XML
DML type:Upsert
Columns for UPDATE:f2
Columns in WHERE clause:f1, f3
Columns for INSERT:f1, f2, f3
Payload:
<?xml version="1.0" encoding="utf-8"?>
<Message type="TERAJMSA">
  <records>
    <row>
      <update>
        <f2>2xx</f2>
      </update>
      <where>
        <f1>111</f1>
        <f3>333</f3>
      </where>
      <insert>
        <f1>111</f1>
        <f2>222</f2>
        <f3>333</f3>
      </insert>
    </row>
  </records>
</Message>

Result:
update of all records
setting f2=2xx
where f1=111 and f3=333

if there's no match with the where condition then insert 1 record
with f1=111 and f2=222 and f3=333

Example 12

XML Payload Format - Delete

Format:XML
DML type:Delete
Columns in WHERE clause:f1, f2, f3
Payload:
<?xml version="1.0" encoding="utf-8"?>
<Message type="TERAJMSA">
  <records>
    <row>
      <f1>a</f1>
      <f2 null='false'>b</f2>
      <f3>c</f3>
    </row>
    <row>
      <f1>111_1</f1>
      <f2 null="true">222</f2>
      <f3>333_1</f3>
    </row>
  </records>
</Message>

Result:
deletion of all records
where (f1=a and f2=b and f3=c)
OR
where (f1=111_1 f2 is null and f3=333_1)

0 Kudos
Reply
Teradata Links
Teradata.com Teradata Partners
Support & Forums
Teradata Support Support Portal / TaYS Teradata Enhancement Requests Teradata Aster Community Communities
Downloads
Documentation Developer Downloads Teradata Express Teradata Aster Express Teradata Aster Development Environment
Community Feedback

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.