Jump to content


Photo

How to Copy records/Data from one table to another table


  • Please log in to reply
2 replies to this topic

#1 nestorvaldez

nestorvaldez
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 25 September 2006 - 03:00 AM

Hi guys Im getting a problem trying to copy  a list of records from one table to another, I was investiganting and I got it:
INSERT INTO TABLE2 (COL1, COL2, COL3) SELECT COL1, COL4, COL7 FROM TABLE1
I think I can do in this way, but the problem is the tables are not identical and I need to insert some varibles in the destination table, and a current date, and so on, so in this way I think I cant do it...

From the table I need to take the data I just need some fields, but the others are variables that are in my script..

How can I solve it.... If anybody can give a solution to this issue..


#2 alpine

alpine
  • Members
  • PipPipPip
  • Advanced Member
  • 756 posts
  • LocationNorway

Posted 25 September 2006 - 04:51 AM

It really depend on the size of your origin table, if only hundred or perhaps thousand rows its no prob to run a insert query within the while loop of your select query. If you have a massive origin table i would create an insert-array from the select, add the extra info to the array and do a bulk insert.

But, aren't you able to alter the table you already have to suit your needs ?

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,025 posts

Posted 25 September 2006 - 11:29 AM

You can inser the current date using CURDATE() function and also add constants to query, so if in your destination table, COL4 should be the current date and COL5 should always be 42

INSERT INTO TABLE2 (COL1, COL2, COL3, COL4, COL5 ) 
    SELECT COL1, COL4, COL7, CURDATE(), 42 
    FROM TABLE1

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users