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
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()