pysqlplus_union.py

def union(self, table_list, name="union", join="UNION"):
    """Pass a list of table names to union them all together. The join
    argument can be changed to alter between UNION/UNION ALL.

    Keyword arguments:
    table_list -- a list of table names, example: to union [d1] and
                  [d2], table_list = ["d1", "d2"]
    name -- the name of the table created by the union (default "union")
    join -- the union type, either "UNION" or "UNION ALL" (default "UNION")
    """
    # initialise the query
    query = "SELECT * INTO ["+name+"] FROM (\n"

    # build the SQL query
    query += f'\n{join}\n'.join(
                        [f'SELECT [{x}].* FROM [{x}]' for x in table_list]
                        )

    query += ") x"  # add end of query

    cursor = self.cnxn.cursor()  # create execution cursor

    cursor.fast_executemany = True  # activate fast execute

    # update user
    print("Executing {} operation for {} tables.".format(join,
                                                         len(table_list)))

    cursor.execute(query)  # execute

    self.cnxn.commit()  # commit union to SQL Server

    # append query to our SQL code logger
    self.query += ("\n\n-- union operation\n" + query)

    print("Union complete.\n")  # update user