Creating Tables from a SQL Select statement

SQL provides a statement for creating tables from select statements.
In MS SQL Server 2005

Create these two tables
1) create table employee(EmployeeId int identity primary key,EmpName varchar(50) not null)

2) create table Cricketer(Name varchar(50) primary key,runs int,catches int,wickets int)

Insert some data
insert into employee values('Champak');
insert into employee values('Popat');


insert into Cricketer values('Champak',18000,300,1600);
insert into Cricketer values('Sachin',17000,250,1000)

Creating the Table
select * into  JoinedTable  from  employee,Cricketer where Name=EmpName

select * from JoinedTable

The Tables

The same thing can be done in Oracle using the following syntax:-

create table OracleEmployee(EmployeeId int primary key,EmpName varchar(50) not null);
create table OracleCricketer(Name varchar(50) primary key,runs int,catches int,wickets int);
insert into Oracleemployee values(1,'Champak');
insert into Oracleemployee values(2,'Popat');
insert into OracleCricketer values('Champak',18000,300,1600);
insert into OracleCricketer values('Sachin',17000,250,1000);

create table  JoinedTable as select *  from  Oracleemployee,OracleCricketer where Name=EmpName;
select * from JoinedTable

 In MySQL the same thing works

create table MySQLEmployee(EmployeeId int primary key,EmpName varchar(50) not null);
create table MySQLCricketer(Name varchar(50) primary key,runs int,catches int,wickets int);
insert into MySQLemployee values(1,'Champak');
insert into MySQLemployee values(2,'Popat');
insert into MySQLCricketer values('Champak',18000,300,1600);
insert into MySQLCricketer values('Sachin',17000,250,1000);

create table  JoinedTable as select *  from  MySQLemployee,MySQLCricketer where Name=EmpName;
select * from JoinedTable 

In MS Access

Create these two tables
1) create table employee(EmployeeId int  primary key,EmpName varchar(50) not null)

2) create table Cricketer(Name varchar(50) primary key,runs int,catches int,wickets int)

Insert some data
insert into employee values(1,'Champak');
insert into employee values(2,'Popat');


insert into Cricketer values('Champak',18000,300,1600);
insert into Cricketer values('Sachin',17000,250,1000)

Creating the Table
select * into  JoinedTable  from  employee,Cricketer where Name=EmpName

select * from JoinedTable