Consolidating data into one table from similar tables - Tibor Karaszi

The basic idea is toi use UNION in a SELECT. Like:

SELECT col1, col2, ... FROM tbl1
UNION ALL
SELECT col1, col2, ... FROM tbl2
UNION ALL
SELECT col1, col2, ... FROM tbl3
UNION ALL...

Of course, above do not create a table or add rows to a table. For this you need to decide whether
to use SELECT INTO (which creates a table) or INSERT with a subselect (which add rows to an existing
table). Examples:

SELECT col1, col2, ... INTO myNewTable FROM tbl1
UNION ALL
SELECT col1, col2, ... FROM tbl2
UNION ALL
SELECT col1, col2, ... FROM tbl3
UNION ALL...


INSERT INTO myNewTable (col1, col2, ...)
SELECT col1, col2, ... FROM tbl1
UNION ALL
SELECT col1, col2, ... FROM tbl2
UNION ALL
SELECT col1, col2, ... FROM tbl3
UNION ALL...



--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Consolidating data into one table from similar tables - dave ballantyne

Sounds best to create a view...

Create View myView
as

Select * from 080501TableAlpha
union all
Select * from 080502TableAlpha
union all
Select * from 080503TableAlpha
......


Dave

Consolidating data into one table from similar tables - Tom Moreau

In that case, you will need to do it through dynamic SQL.  You can get the
names by running:


select
table_name
from
information_schema.Tables
where
table_name like '%TableAlpha'

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


On May 7, 3:51 am, dave ballantyne <syml...@nospammeever.yahoo.com>

You are suggesting that I hard code the table names in a view?  That's
actually what I am trying to avoid.  I am hoping to take advantage of
the tables collection somehow.

Consolidating data into one table from similar tables - dave ballantyne

in that case

create a empty table with the same structure

Then use dynamic SQL to build a SQL string to insert the data from a
source table and then Drop (or rename) it

This should get you started on dynamic sql
http://www.sommarskog.se/dynamic_sql.html

Dave

Consolidating data into one table from similar tables - robboll

Hello SQL gurus!  Is there a simple way to do the following using
TSQL?  Here's a model of the problem.

There are 10 tables that are structurally identical and named
similarly:

080501TableAlpha
080502TableAlpha
080503TableAlpha
.
.
.
080510TableAlpha

I would like to append the contents of all of them into one new table:
TableAlpha

Without doing them individually, what procedure can I run to automate
the process?

Thanks for any help with this.

RBollinger

Consolidating data into one table from similar tables - robboll

On May 7, 3:51=A0am, dave ballantyne <syml...@nospammeever.yahoo.com>

You are suggesting that I hard code the table names in a view?  That's
actually what I am trying to avoid.  I am hoping to take advantage of
the tables collection somehow.

Consolidating data into one table from similar tables - Stuart Ainsworth

Can you clarify the problem you're facing?  Do you want to move the
data into a single table and get rid of the other tables?  Are you
creating (and possibly) dropping tables daily?  Is this a one-time
conversion or do you need to do this on an ongoing basis?

Consolidating data into one table from similar tables - robboll

On May 7, 8:12=A0am, Stuart Ainsworth <stuart.ainswo...@gmail.com>
e:
e

t -

This is a one-time thing.  From my point of view the production
database of designed poorly where around 40 tables are created daily
with a date stamp.  Now there are literally thousands of tables over
several years.  So I am looking for a method of consolidating the data
(e.g., 080501TableAlpha, etc.) into one table.  The routine would have
to ignore the date.  If it says "TableAlpha" anywhere append it's data
into new table: TableAlpha.  Maybe skip through a tables collection to
do it.  This is how to do it using MS Access.  I am looking for a
solution using DTS or SSIS to a seperate database on the same server.
The solution is not for the light hearted.

Thanks!

RBollinger

Consolidating data into one table from similar tables - Stuart Ainsworth

You can do one of two things:

1. Build a partitioned view (assuming this is SQL 2000), and leave the
current tables alone.  This may mean more work for yuo, because you'll
need to make those tables ready to be part of a partitioned view
(adding constraints, etc).

2. Create your single table, and move all of the data into it.  (In
SQL 2005, you can even use a partitioned table which is much more
transparent than a partitioned view, but has several of the same
benefits).

In either case, you can build a SQL statement from the
Information_Schema views; I'll give an example of the latter option:

SELECT 'SELECT * FROM ' + TABLE_NAME + ' UNION ALL ' FROM
INFORMATION_SCHEMA.TABLES WHERE Table_Name LIKE '%TableAlpha'

Cut and paste the results of that query into a new window, delete the
last 'UNION ALL', and add an INSERT clause at the top.  Note that this
may run into issues if they used a primary key that was intrinsinc to
the single table (like an Identity).

Hope that gets you started. If you want more help,  you'll need to
provide the table structures for the target table and at least one of
the source tables.

Stu