Code for Iteratively Adding New Columns in SQL Server Table-CodePudding

I have a list of columns to add into a SQL Server DB.

colList = ['title', 'docDate', 'coleadAgency',
           'usBusinessName', 'description',
           'acquCountry', 'ubo1Value', 'naicsCode',
           'mitigation']

I have a table called TAbby, and want to add these columns to it. I tried

for col in colList:
    cur.execute(""" ALTER TABLE TAbby ADD (?) NVARCHAR(255) """, col)
    cur.commit()

But I got error:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '('. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

CodePudding user response：

You cannot parameterize identifiers like table or column names. Parameters are reserved for literal values. Therefore, dynamically string format the column name in SQL statement. (Square brackets around column name is to escape special characters, spaces, or reserved words).

for col in colList: 
    cur.execute(f"ALTER TABLE TAbby ADD [{col}] NVARCHAR(255)") 
    cur.commit()

Actually, TSQL supports multiple, comma-separated columns in ADD clause. So, consider building with list comprehension:

new_cols = ", ".join([f"[{col}] NVARCHAR(255)" for col in colList])

cur.execute(f"ALTER TABLE TAbby ADD [{new_cols}]") 
cur.commit()