For loop Teradata insert statement

Question

I am pretty new to Python and looking for some assistance.

I have a large number of Teradaat tables and I am looking to take a single field from each of theose tables and put it into a new table. Instead of doing it manually I have created a list of all the table names and I am trying to create a for looped insert statement based on those name.

I have created the follwoing code that runs without errors but nothing is inserted. When I run the print statement and try it in Teradata that works so not sure why it is not working.

Any help would be much appreciated.

            cur.execute ("SELECT concat('CP.',TableName)FROM    DBC.TablesV WHERE   TableKind = 'T' and     DatabaseName = 'CP' and     TableName like 'ALB_%' and     length (tablename)=23;")
            for row  in cur.fetchall():
                if row[0] not in [""]:
                    #print(f"insert into CP.test1234 select distinct campaign_start from {row[0]};")
                    f"insert into CP.test1234 select distinct campaign_start from {row[0]};"

I was expecting campaign start date from each of the tables in the list to be inserted into CP.test1234

D.lola · Accepted Answer · 2024-08-13 09:41:54Z

0

The use of f-string is for formatting not inserting, what you want is the execute.

I have provided a code snippet to guide you:

# if cur is your cursor connected to Teradata
cur.execute("""
    SELECT concat('CP.', TableName)
    FROM DBC.TablesV
    WHERE TableKind = 'T'
      AND DatabaseName = 'CP'
      AND TableName LIKE 'ALB_%'
      AND length(tablename) = 23;
""")

# Fetch all table names you want
table_names = cur.fetchall()

# loops through your table names and execute the insert statement for each
for row in table_names:
    if row[0]:  # checks if table is not None
        # the insert statement
        insert_statement = f"INSERT INTO CP.test1234 SELECT DISTINCT campaign_start FROM {row[0]};"
        
        # insert statement
        cur.execute(insert_statement)

# Commit changes
cur.connection.commit()

answered Aug 13, 2024 at 9:41

D.lola

2,3222 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

D.lola Over a year ago

@RichardGrant no worries happy to help :)

Collectives™ on Stack Overflow

For loop Teradata insert statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related