|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Combining archive tables into a single tableI 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.
Show quote
"Kumar" <Ku***@discussions.microsoft.com> wrote in message Take a look at the Partitioned Views topic in Books Online.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. -- 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) Show quote "David Portas" wrote: > "Kumar" <Ku***@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 > -- > > > 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/
Show quote
"Kumar" <Ku***@discussions.microsoft.com> wrote in message That's easily fixed then - add the columns to the older tables. Why would 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: > >> "Kumar" <Ku***@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 >> -- >> >> >> > -- 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 be a problem? > --And i have to add a column to uniquely represent which version it is(Is Yes you do have to add such a column. Without that your design is weak, > there any other solution to ditinguish the versions) 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 -- 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 Like this:> > 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 > 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 --  |
|||||||||||||||||||||||
Other interesting topics