Thread: Move columns from different tables to one table

How to select columns from different tables and move all those columns in one table for example we

select sal from dept where sal>1000
(in this we get one column regarding sal greater than 1000)
select deptno from dept;

overall we get two columns .my qns is how to put these two different columns in one table

Joins are used to join different tables
for about joins follow this link
create table deptwisesal(deptno number(2),sal number(5));


insert into deptwisesal select d.deptno,e.sal from emp e,dept d where e.deptno=d.deptno and e.sal>1000


drop table deptwisesal;

You can't move a column of existing table to another table. Just follow as suggested in the previous post for best alternative.

u can do this by using select into query.........

For Ex,

Select dno,sal into depsal from dept,emp where sal>1000
select * fom depsal


then we get the new table depsal with dno,sal whose sal >1000........

Originally Posted by ushalakshmi

u can do this by using select into query.........

For Ex,

Select dno,sal into depsal from dept,emp where sal>1000
select * fom depsal


then we get the new table depsal with dno,sal whose sal >1000........

Select into is used in PL/SQL to dump value of a column to a variable. It will not create a new table.

In a single statemt you can do it in the following way
CREATE TABLE deptwisesal AS
SELECT emp.sal, dept.deptno
FROM emp,dept
where emp.deptno = dept.deptno;

Originally Posted by ushalakshmi

Select dno,sal into depsal from dept,emp where sal>1000
select * fom depsal

that will not create a table for you .

There are 2 condition for create table which consist of columns from 2 table

1. Table having common column to join 2 table
2. Table not having common column to join 2 table

If there is a linking column (common column on the basis of which 2 tables can be connected) between 2 table then u can create table like this

create table dept_emp as
select dept.dname, emp.ename
from emp, dept
where emp.deptno = dept.deptno;


If 2 table from which column is to be taken are not having same column
then u need to use joins

--say for example i have 2 table
create table t1 (num1 number, num2 number);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);
insert into t1 values(4,4);

create table t2 (str1 varchar2(20), str2 varchar2(20));
insert into t2 values('a','a');
insert into t2 values('b','b');
insert into t2 values('c','c');
insert into t2 values('d','d');

-- To create table with data
create table t1_t2 as
select num1,NULL str1 from t1
UNION
select NULL,str1 from t2;

select * from t1_t2;