I am dealing with the situation in sql server where a database contains the multiple tables with similar table name (ex total_0122, total_0622) and same column. I wanted to calculate the percentage difference from all those tables by taking particular columns data. My approch is
- list all the tables present in the database with name like %Total_%
- Then concatenate/join the all the tables together
- Read the table content
- lastly find the percentage diffrence.
I am able to list all the tables present in the database and able to concatenate it which is step 1 and 2. for which i have performed
''' SELECT
sys.columns.name AS ColumnName,
tables.name AS TableName
FROM
sys.columns
JOIN sys.tables ON
sys.columns.object_id = tables.object_id
WHERE
sys.tables.name LIKE '%total_%
which gives me an output as
ColumnName TableName
email total_0122
country total_0122
revenue total_0122
email total_0622
country total_0622
revenue total_0622
I am not able to figure out how to read those tables into one table along with the column values (email, country, revenue...) I am new sql. Any help will be appreciated. Please advice.