Combining archive tables into a single table

Hi,
I had a table in my database which will update every month ... so we used to
update the table every month and stored the archieve tables in a seperate
database.
--ID is the primary key for this table and all historical of the record will
have the same ID
Now I have to combine all those tables(Around 30 tables and each had around
3k columns) into one table based on the primary key of current version table
.
-Each Archieve table had one Unique Cycle_id
Note: The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
Now,the structure of my new table can be the same as "current version" table
(this month) with additional field cycle_id
Pls try to help me guys, which way is better to achieve this."Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Hi,
> I had a table in my database which will update every month ... so we used
> to
> update the table every month and stored the archieve tables in a seperate
> database.
> --ID is the primary key for this table and all historical of the record
> will
> have the same ID
> Now I have to combine all those tables(Around 30 tables and each had
> around
> 3k columns) into one table based on the primary key of current version
> table.
> -Each Archieve table had one Unique Cycle_id
> Note: The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the time
> Now,the structure of my new table can be the same as "current version"
> table
> (this month) with additional field cycle_id
> Pls try to help me guys, which way is better to achieve this.
Take a look at the Partitioned Views topic in Books Online.
David Portas
SQL Server MVP
--|||David,
Thats a good idea ...i just went through that ,but the problem is to make
partioned view on partioned tables we need to have all smilar structure
tables.I think then only it will be possible to combine(Union) all those and
show it as One Table.
But in my case,as I said
-- The historical tables may differ very slightly in structure from the
current version,some columns may be missing that were added over the time
--And i have to add a column to uniquely represent which version it is(Is
there any other solution to ditinguish the versions)
"David Portas" wrote:

> "Kumar" <Kumar@.discussions.microsoft.com> wrote in message
> news:EAA5F7B1-C732-41AE-B4DE-5A0F21D46BA5@.microsoft.com...
> Take a look at the Partitioned Views topic in Books Online.
> --
> David Portas
> SQL Server MVP
> --
>
>|||"Kumar" <Kumar@.discussions.microsoft.com> wrote in message
news:18358F29-A0FB-45F5-9586-E30A127703E9@.microsoft.com...
> David,
> Thats a good idea ...i just went through that ,but the problem is to make
> partioned view on partioned tables we need to have all smilar structure
> tables.I think then only it will be possible to combine(Union) all those
> and
> show it as One Table.
> But in my case,as I said
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
>
> "David Portas" wrote:
>
> -- The historical tables may differ very slightly in structure from the
> current version,some columns may be missing that were added over the
> time
That's easily fixed then - add the columns to the older tables. Why would
that be a problem?

> --And i have to add a column to uniquely represent which version it is(Is
> there any other solution to ditinguish the versions)
Yes you do have to add such a column. Without that your design is weak,
whether or not you choose to use a partitioned view. It's not generally a
good idea to have multiple tables of the same structure with duplicate data.
Any reason you didn't or don't combine them as a single table? Re-reading
your post it seems that was your actual question. The answer is just to
insert all the data to a common table using INSERT statements. Maybe I'm not
quite understanding what your problem is. Perhaps it would help if you
posted some sample DDL.
David Portas
SQL Server MVP
--|||If archived tables have less columns than the current table, simply add null
values to the union selects where the actual values are missing.
Also add a column that will contain a distinct value for each of the
partitions.
If you post some DDL we can give you a better illustration.
ML
http://milambda.blogspot.com/|||Thanks ML,David
My table is like huge one with almost 30 columns ..any way iam displaying
some of those for demonstration
--Lets say,this is my current version table and the newly creating should be
in this format
CREATE TABLE [dbo].[COPY_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL--This is newly added column and which is
not there in previous versions
)
I had another table "Cycle",which maintains ids of the previous version
tables..like
Cycle_ cycle Publication data table name
name
1005 2005-11-04 22:59:18.653 dbo.GLOBAL_CC_MASTER_1005
0905 2005-10-07 13:35:15.330 dbo.GLOBAL_CC_MASTER_0905
0805 2005-09-08 02:26:43.873 dbo.GLOBAL_CC_MASTER_0805
0705 2005-08-08 22:13:04.013 dbo.GLOBAL_CC_MASTER_0705
0605 2005-07-07 19:03:43.020 dbo.GLOBAL_CC_MASTER_0605
0505 2005-06-06 17:34:03.517 dbo.GLOBAL_CC_MASTER_0505
0405 2005-05-10 12:15:12.027 dbo.GLOBAL_CC_MASTER_0405
0305 2005-04-11 23:38:59.073 dbo.GLOBAL_CC_MASTER_0305
Now I have to add all these tables into one table
"Archieve_GLOBAL_CC_MASTER" and has to include 'cycle_name' as primary key
along with 'id_pk', which should be look like:
CREATE TABLE [dbo].[Archieve_GLOBAL_CC_MASTER] (
[id_pk] [int] IDENTITY (1, 1) NOT NULL ,-- Primary key and all historical
versions will have same id_pk
[CYCLE_NAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,--composite Primary key,New column which is not there in current table
[BATCH_ID] [int] NULL ,
[SOURCE_ID] [int] NULL ,
[LEDGER_ID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYABLES_ID] [int] NULL
[CLAIM_STATUS] [int] NULL
)
I think this should help you for better understanding|||It may be late and I may need glasses, but I think you've just come up with
a
solution. Create the new table and migrate all data from the old tables,
creating missing values at insert.
ML
http://milambda.blogspot.com/|||Kumar wrote:
> Thanks ML,David
> My table is like huge one with almost 30 columns ..any way iam displaying
> some of those for demonstration
> --Lets say,this is my current version table and the newly creating should
be
> in this format
>
Like this:
INSERT INTO [dbo].[archive_global_cc_master]
(id_pk, cycle_name, batch_id, source_id, ledger_id, payables_id,
claim_status)
SELECT id_pk, 1005, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_1005
UNION ALL
SELECT id_pk, 0905, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0905
UNION ALL
SELECT id_pk, 0805, batch_id, source_id, ledger_id, payables_id,
claim_status
FROM dbo.GLOBAL_CC_MASTER_0805
UNION ALL ... etc
I'm not clear what you want to do with your keys. Are other tables to
reference Archive on a surrogate IDENTITY key? If so you'll want to
assign a new IDENTITY in which case id_pk won't be IDENTITY in your
archive table.
Are you sure all those other columns need to be nullable? Are you sure
you have an alternate key in each table? If not you may have
duplicates. I'm not convinced that you have a sound design here to
start with, but that could be a mistaken assumption given that this is
just a fragment.
David Portas
SQL Server MVP
--