0

I'm using Oracle SQL. I have 3 tables which including each one 80 columns with the same columns names. I want to join the tables into a new table, but i supposed to get an error due to a duplicated naming.

I don’t what to rename 240 columns. Is there any other solution?

Here is my tables:

T1 table

c1 int,
c2 int,
..
..
..
c80 int

T2 table

c1 int,
c2 int,
..
..
..
c80 int

T3 table

c1 int,
c2 int,
..
..
..
c80 int

And here is my join:

Create table NewTable as
select * 
from T1
   inner join T2
      on T1.id = T2.id
   inner join T3
      on T1.id = T3.id

Is there any solution for such as problem? For example - presenting a full column name including table name (t1.c1, t1.c2...).

Omri
  • 1,436
  • 7
  • 31
  • 61
  • replace the wildcard with explicit table.column name and use an alias. – OldProgrammer Dec 03 '14 at 17:23
  • t3.id=t3.id is suspicious. what's the exact error message as oracle usually dones't care about duplicate columns names, it appends #s however given it usually just adds the next number t2.c1 becomes t2.c11, but t1.c11 already exists... that might be the problem... in which case without renaming columns or using dynamic SQL a view, or a cte, I think you're out of luck if you want to still do the select * – xQbert Dec 03 '14 at 17:23
  • The "t3.id=t3.id" was a typo. I fixed it. Anyway, i'm getting "duplicate column name error". The table name prefix wasn't appended. – Omri Dec 03 '14 at 17:26

1 Answers1

0

something like:

select T1.c1, T1.c2, T2.c1 as c1_T2, T2.c2 as c2_T2

etc. should work.

Edited: Or maybe you want to UNION the records into one table. Maybe have an extra column describing the source of the records, if needed to differentiate them.

Mafu Josh
  • 2,523
  • 1
  • 23
  • 25