AGKRInsertSelect.py

# Copyright 2023 by Teradata Corporation. All rights reserved.

# This sample program demonstrates Insert/Select with Auto-Generated Key Retrieval (AGKR).

import teradatasql

with teradatasql.connect (host="whomooz", user="guest", password="please") as con:

    with con.cursor () as cur:

        sTableName = "agkrdemo"
        cur.execute ("drop table " + sTableName, ignoreErrors=3807)
        cur.execute ("create table " + sTableName + " (c1 integer generated by default as identity, c2 varchar(100))")
        try:
            print ("Using AGKR option C to return identity column values only")
            cur.execute ("{fn teradata_agkr(C)}insert into " + sTableName + " (c2) select InfoKey from DBC.DBCInfo")

            print ("All identity column values are retured in one result set")
            [ print (row) for row in cur.fetchall () ]

            print ("Using AGKR option R to return entire inserted rows")
            cur.execute ("{fn teradata_agkr(R)}insert into " + sTableName + " (c2) select InfoData from DBC.DBCInfo")

            print ("All inserted rows are retured in one result set")
            [ print (row) for row in cur.fetchall () ]

            print ("Final contents of table")
            cur.execute ("select * from " + sTableName + " order by 1")
            [ print (row) for row in cur.fetchall () ]

        finally:
            cur.execute ("drop table " + sTableName)