Copy one database table's contents into another on same database

Question

I'm a bit of a newbie with the workings of phpmyadmin. I have a database and now there are 2 parts within it - the original tables jos_ and the same again but with a different prefix, say let's ****_ that will be the finished database.

This has come about because I am upgrading my Joomla 1.5 site to 2.5. I used a migration tool for the bulk of the new database but one particular piece of information did not transfer because the new database has a different structure.

I want to copy the entire contents of jos_content, attribs, keyref= across to ****_content, metadata, "xreference"."VALUE" if that makes sense. This will save manually typing in the information contained within 1000s of articles.

jos_content, attribs currently contains

    show_title=
    link_titles=
    show_intro=
    show_section=
    link_section=
    show_category=
    link_category=
    show_vote=
    show_author=
    show_create_date=
    show_modify_date=
    show_pdf_icon=
    show_print_icon=
    show_email_icon=
    language=
    keyref=41.126815,0.732623
    readmore=

****_content, metadata currently contains

    {"robots":"all","author":""} 

but I want it to end up like this

    {"robots":"","author":"","rights":"","xreference":"41.126815,0.732623","marker":""}

Could anyone tell me the SQL string that I would need to run to achieve this please?

If it makes any difference I have manually changed about 300 of these articles already and thought there must be a better way.

Edit: Being nervous of trying this I would like to try and find the exact syntax (if that's the right word) for the SQL Query to run.

The value I want to extract from the source table is just, and only, the numbers next to keyref= and I want them to turn up in the destination table prefixed by "xreference". - so it shows "xreference"."VALUE" with VALUE being the required numbers. There is also an entry - ,"marker":"" that is in the destination table so I guess the Query needs to produce that as well?

Sorry for labouring this but if I get it wrong, maybe by guessing what to put, I don't really have the knowledge to put it all right again....

Thanks.

Answer 1

Please Try it

insert into tableone(column1,column2) select column1,column2 from Tablesecond

if You have not Table another Daabase Then This query

select * into anyname_Table from tablesource