SQL Server Combine All Tables
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Simple, I have many tables in a database that I want to be combined (UNION?). For instance, if I have the following tables:

ALPHA:

ID      Product    Date
1       Rice       2017-05-06
2       Milk       2017-05-11

BRAVO:

ID      Product    Date
3       Sugar      2018-03-05
4       Bread      2018-03-12

I want the result to be:

ID      Product    Date
1       Rice       2017-05-06
2       Milk       2017-05-11
3       Sugar      2018-03-05
4       Bread      2018-03-12

Keep in mind that the query has to be generic since those are not all the columns in the tables. Also, the query has to only combine tables that are not numeric. I was trying to use WHERE ISNUMERIC(name) = 0

Whoever gets it first, gets the bounty

UPDATE:

-The query must be generic since I will continue adding tables to the database, such as CHARLIE, DELTA, ECHO, etc

-The target tables have the same schema

-All I want is to visualize the result, no need to store it in a new table

-By "tables that are not numeric" I mean that there are tables in my database that have names like these: [2009], [2010], [2016], and such. Those tables have different schemas and I obviously don't want them in the combination. But I want everything else that is in the database to be combined

  • I tried something like this: SELECT * FROM Sys.Tables WHERE ISNUMERIC(name) = 0, but it is not exactly what I want. I want all the columns from the tables merged, combined
Hi, does those target tables to be combined have the same schema ? and do you need to store the result in new table ?
SilverHood Apps 5 months ago
Did you mean not numeric columns?
enderdba 5 months ago
What do you mean by tables that are not numeric?
Codeword 5 months ago
Please read my update for an answer to all your questions
SiliconV 5 months ago
Do u wanna get this implemented via php?
Codeword 5 months ago
You really should use another Schema for this, instead of a table for each year/person. Table names should not convey data, only aggregation. You should instead be using a "date" column and a "persons" column.
gabrielsimoes 5 months ago
@gabrielsimoes you are right, but it is a bit too late for me to do that for a few good reasons. Please if you can solve this, go ahead
SiliconV 5 months ago
Are you okay if u need to add an extra table to keep track of tables you added, let me know
Codeword 5 months ago
Tags
sql-server

Crowdsource coding tasks.

3 Solutions


For distinct values

SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM table3

For Duplicaticate values

SELECT * FROM table1 UNION ALL SELECT * FROM table2 UNION ALL SELECT * FROM table3
Thanks @Codeword. I am looking for a generic query since I will be adding more tables to the database in the future
SiliconV 5 months ago
Okay, do you want this implemented via php.
Codeword 5 months ago
No, just a query
SiliconV 5 months ago
Are you okay if u need to add an extra table to keep track of tables you added, let me know
Codeword 5 months ago
I am not understanding your question
SiliconV 5 months ago
I Mean you will need to add an extra table which will store the table name whenever you create a new table, the new table name will be added to this table so that we have a track of the tables added to the database and execute as a single query.
Codeword 5 months ago
No problem, let's make it a temporary table. I don't want to store anything extra in the database
SiliconV 5 months ago

If you want to get all columns from all tables use asterisk (*), the most generic one.If you want to select only some columns for your query, just SELECT the columns you need to combine.

EXAMPLE FOR ALL COLUMNS FROM ALL TABLES

SELECT * FROM CHARLIE 
UNION 
SELECT * FROM ALPHA
UNION
SELECT * FROM ECHO

... and so goes on,

If you got duplicate values and want them to show on the result (ID, for example) you need to change the UNION to UNION ALL

EXAMPLE FOR OTHER COLUMNS

SELECT product, datef FROM CHARLIE 
UNION 
SELECT product, datef, description FROM ALPHA
UNION
SELECT product, datef FROM ECHO
@enderba I need something that I won't have to modify ever again. In other words, it will account for any addition. Check my new update
SiliconV 5 months ago

Hi SiliconV, heres a working solution.. I've created a stored procedure that basically does the UNION in smart way as below. I hope that suffices.

UPDATED : please call DROP PROCEDURE SeeMergedData; before doing this

create procedure SeeMergedData @MyYear NVARCHAR(4)
AS
BEGIN
Declare @tablename nvarchar(MAX),
    @Sql NVARCHAR(MAX) = ''

Declare curP cursor For --fetches the table names  
  SELECT TABLE_NAME FROM <<YourDataBaseName>>.INFORMATION_SCHEMA.TABLES
  WHERE TABLE_TYPE = 'BASE TABLE' 
  and ISNUMERIC(TABLE_NAME) = 0

OPEN curP 
Fetch Next From curP Into @tablename --feed table name one by one

While @@Fetch_Status = 0 Begin
 -- construct the sql
 SET @Sql = @Sql + 'SELECT *,''' +@TableName + ''' as From_Table 
                   FROM <<YourDataBaseName>>.dbo.' + QUOTENAME(@TableName) 
                    + ' WHERE YEAR(date) = ' + @MyYear
Fetch Next From curP Into @tablename
if @@Fetch_Status = 0
 SET @Sql = @Sql + ' UNION ' -- Replace with UNION ALL if needed
If @@Fetch_Status != 0
 SET @Sql = @Sql + ' ORDER BY date DESC' -- Replace with ASC for oldest record first
End -- End of Fetch

EXECUTE sp_executesql @Sql
Close curP
Deallocate curP

END -- stored procedure

Calling

EXEC SeeMergedData <<Year>>

eg. EXEC SeeMergedData 2018

OLD

Stored Procedure [created only once]:

create procedure SeeMergedData as
BEGIN
Declare @tablename nvarchar(50),
        @Sql NVARCHAR(MAX) = ''

Declare curP cursor For --fetches the table names  
   SELECT TABLE_NAME FROM <<YourDataBaseName>>.INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' 
   and ISNUMERIC(TABLE_NAME) = 0

OPEN curP 
Fetch Next From curP Into @tablename --feed table name one by one

While @@Fetch_Status = 0 Begin
   -- construct the sql
   SET @Sql = @Sql + N'SELECT * FROM <<YourDataBaseName>>.dbo.' + QUOTENAME(@TableName) 
   Fetch Next From curP Into @tablename
   if @@Fetch_Status = 0
     SET @Sql = @Sql + ' UNION ' -- Replace with UNION ALL if needed
   End -- End of Fetch

EXECUTE sp_executesql @Sql
Close curP
Deallocate curP

END -- stored procedure end

To use this whenever you need to analyze the data, call it in one line as:

EXEC SeeMergedData

Will be showing o/p in comments shortly;

Advantage: 1) No new table is introduced 2)single line instead of large select.

Good luck.

O/p Stored Procedure - https://ibb.co/g9tA78 ... Calling it - https://ibb.co/hD6xn8
ps: Had table1, table2, [2009], it shows data from all tables leaving 2009
SilverHood Apps 5 months ago
Hi @SiliconV, any response?
SilverHood Apps 5 months ago
Hi, I have a column in the tables called [Date], can you make it so anytime I call the stored procedure I specify the year? Lastly, I am visualizing it now and I can't tell to which table each record belongs to, can you make it so that there is an extra column or something alike with the name of the table each record belongs to?
SiliconV 5 months ago
Ok great its working. I do the necessary changes. Thanks
SilverHood Apps 5 months ago
Hi @SiliconV included both Year and TableName addon, kindly see the updated solution.. :) o/p: https://ibb.co/b68Gzy
SilverHood Apps 5 months ago
ps: 4th update added column name for the source table
SilverHood Apps 5 months ago
This is working exactly as needed. One question, is it also being sorted by [Date] column?
SiliconV 5 months ago
Great.. Ans: No.. By default it sorts the result set by the 1st column of the result set (unless explicitly specified by order by clause)
SilverHood Apps 5 months ago
Please add that clause for me
SiliconV 5 months ago
Ps: If you need to order that modify @Sql statement in the end as + @MyYear + ' ORDER BY date DESC' to show latest entry first..
SilverHood Apps 5 months ago
Ok, follow the same step.. drop then create.. i'll update the answer
SilverHood Apps 5 months ago
Done.. This should do.. incase you need oldest entry first then use this instead + @MyYear + ' ORDER BY date ASC'
SilverHood Apps 5 months ago
Thanks, that should be all, but now I am getting this error when I try to call the stored procedure: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'UNION'. Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'UNION'.
SiliconV 5 months ago
Ok let me check.. ps: Did you copy the updated solution above ?
SilverHood Apps 5 months ago
Yes, indeed. It only give me the error when + ' ORDER BY date DESC' is added
SiliconV 5 months ago
Ok fixed it, updating the solution..
SilverHood Apps 5 months ago
Thank you. I would like to point out that your instructions and the images you provided as instructions were very helpful. Good work
SiliconV 5 months ago
You're welcome and Thanks SiliconV. Though unable to receive bounty. https://ibb.co/e7AMXJ ideas?
SilverHood Apps 5 months ago
I just looked at my bank account and Bountify has taken the money off it already. Let's contact them and keep me posted
SiliconV 5 months ago
Oh ok.. I'll have a word with them and that be great keeping posted.. Have a good day.. Good night for me (India)
SilverHood Apps 5 months ago
@SilverHoodApps Congratulations! Note that bounties are not instantly delivered; rather they're sent within 48 hours after they've been awarded.
bevan 5 months ago
@Bevan Received bounty 4 hours ago post error. Thanks alot :) @SiliconV Cheers!
SilverHood Apps 5 months ago
Excellent!
SiliconV 5 months ago
View Timeline