What is the easiest way to accomplish this.

I have two similar tables in two databases db1.T1 and db2.T1

They are supposed to have identical structure/fields but are off by a few columns now.

I want to copy a few records from one table to the other so i used CTAS

insert into db2.T1 select * from db1.T1;

it failed because of those a few extra columns in db1.T1 which are in the middle of the table.

The tables has about 150 columns. i can do the copy by selecting each column but it is tedious.

1) is ther a way to create the select dynamically from data dictionary so it will work.

2) what would be the best way to get the table structure match by  adding those missing columns.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Assuming that the column names are the same, you should be able to run this query to get the list:

select column_name from dba_tab_columns where table_name = 'T1' order by column_position;

You want to run that in db2.  Then you can use that list to generate the select statement from db1.T1.
sam15Author Commented:
yes column names are the same.
but that give you a columnar list with one column name on each line.
I would need the comma separator and remove all the blank space after each column name.
johnsoneSenior Oracle DBACommented:
If you want a comma, add one:

select column_name || ',' from dba_tab_columns where table_name = 'T1' order by column_position;

The extra space at the end of the line is a "feature" of SQL*Plus.  If you use "set trimspool on", then it will remove the space at the end of the line.

Basically what you would have to do with the output is put this in front of it:

insert into db2.t1 select

Then after it add:

from db1.t1;

You could do get it in one command like this:

select 'insert into db2.t1 select' from dual
union all
select column_name || ',' from dba_tab_columns where table_name = 'T1' order by column_position
union all
select 'from db1.t1;' from dual;

I was trying to give you a starting point to work from.  If you figure out how to do it yourself, then you can do it on your own the next time.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

johnsoneSenior Oracle DBACommented:
Of course, that query is going to give you a comma after the last column that you are going to have to remove.
sam15Author Commented:
Yes. I guess there is no way to get the columns right after each other. it has  to be one column name on each line. right?

How would you get the tables in sync if for example T1 in DB1 has 2 columns in the middle that need to be added to T1 in DB2.
johnsoneSenior Oracle DBACommented:
The columns don't have to be in the same order.  As long as the select list lines up with the table you are inserting into, you shouldn't have a problem.  To be absolutely sure, you should specify the column list on both the insert and the select.

There are tricks that you can use to get the columns on one line, but that really isn't necessary.  Also, if you are going to run this back through SQL*Plus, you could exceed the allowed length of a line.  It is just safer to list them all out on separate lines.
awking00Information Technology SpecialistCommented:
>>How would you get the tables in sync if for example T1 in DB1 has 2 columns in the middle that need to be added to T1 in DB2. <<
Are you saying that you want the columns to be in the same position in each table or are you saying is there a way to determine the columns that don't exist in DB2.T1 and add them to the table (adding columns to the table always occurs at the end)?
sam15Author Commented:
yes, i want to add the 2 columns in the same position so in the future they both look identical. It is easier for copying data and looks better.
johnsoneSenior Oracle DBACommented:
That is not possible.  You can only create columns at the end of the table.  What you would have to do is create a new table with the columns in the order you want, then insert the data into that table, drop the original table and rename the new table.

You would have to take care of grants, indexes, constraints, etc. on the new table as well.
awking00Information Technology SpecialistCommented:
As johnsone points out you cannot add columns in the middle of an existing table. You would need to drop the DB2.T1 table and re-create it -
create table DB2.T1 as select * from DB1.T1;
As he also notes, you would need to then re-create any indexes, constraints, etc. on the new table.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.