Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    7

    Lightbulb Unanswered: Clone dynamic table

    hi guys ,

    Good to be a part of you all! Let me get straight to work
    Is there a way to clone dynamic table? I mean to say that I do 'show create table' and 'create table alias-name' and then 'insert option' but they result only in giving me data currently present in the table. My requirement is a table which keeps on adding values in the cloned table as and when I add data to the original table .. Now as a good database programmer you are supposed to use data in a cloned table for all your operations going back to the basics right !

    Well I'm not very much familiar with replication concepts and all I know about it is replication is used for long-distance and different servers! Can it be implemented here pertaining to this context in the same server which more or less performs like a dynamic cloned table is what I name it?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sandyssn
    Now as a good database programmer you are supposed to use data in a cloned table for all your operations going back to the basics right !
    no, that's not right

    can you please explain with more detail why you need a clone table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you talking about an audit / history table?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2009
    Posts
    7

    Re: Clone dynamic table

    Well I would explain it evidently!

    First I create a table called friends with so-and-so parameters ..

    create table friends(name varchar(25) not null, degree varchar(10) not null, initials varchar(5) not null, marks decimal(4,1) not null, datentime datetime not null primary key);

    and then I insert data as follows ..

    insert into friends values('femil','bsc','a',59,'2008-12-09 20:20:57');
    insert into friends values('arun','bsc','k',64,'2008-12-09 20:21:57');
    insert into friends values('edwin','bsc','c',72,'2008-12-09 20:22:57');
    insert into friends values('aravind','bsc','a',58,'2008-12-09 20:23:57');

    Now I have a set of records in my table. Now I clone the table by

    show create table friends \G;
    *************************** 1. row ***************************
    Table: friends
    Create Table: CREATE TABLE `friends` (
    `name` varchar(25) NOT NULL,
    `degree` varchar(10) NOT NULL,
    `initials` varchar(5) NOT NULL,
    `marks` decimal(4,1) NOT NULL,
    `datentime` datetime NOT NULL,
    `filename` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`datentime`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.00 sec)


    and create an alias-table

    CREATE TABLE `friends2` (
    `name` varchar(25) NOT NULL,
    `degree` varchar(10) NOT NULL,
    `initials` varchar(5) NOT NULL,
    `marks` decimal(4,1) NOT NULL,
    `datentime` datetime NOT NULL,
    `filename` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`datentime`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

    Now I insert my 'friends' table by

    insert into friends values('sandeep','bsc','s',75,'2008-12-09 20:24:57');

    Well, here comes my question now ..

    This insertion is obviously not reflected in 'friends2'. What should I do so that this insertion is also reflected or added in the cloned table here 'friends2' not only once but als o infinite number of times i insert or update or delete rows from the original table 'friends'

    Note: Please dont tell me to insert it at the beginning itself. This is just a prototype and I'm doing a real-time concept whatever I told you! Any ideas friends?

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you could write a trigger

    but my question was... why do you need a cloned table in the first place?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Perhaps he's thinking of replication?

    Still can't think of why he'd want to do anything other than read from the original table though. Perhaps he needs to revisit those basics he mentioned?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •