Create tables from master table

Hello all, I am looking for some help with the following:

I need to create individual tables from a master table, all columns will be copied and the selection is based on the values within one column - I need to create as many tables as there are distinct values in this column (there are around 60 distinct values).

I have a PL/SQL command to do this but this doesn't work in Transact-SQL, can this be converted to work? Is there a convert facility somewhere for this?

The PL/SQL command I have is:

set serveroutput on size 1000000

declare
cursor c_dept is
select distinct deptno
from emp;

v_sql varchar2(1000);
begin
for r_dept in c_dept loop
dbms_output.put_line ('dept: '||r_dept.deptno);

v_sql := 'create table kl_'||r_dept.deptno||
' as (select * from emp where deptno = '''||r_dept.deptno||''')';

dbms_output.put_line(v_sql);

execute immediate v_sql;
end loop; -- dept
end;
/

Any help would be greatly appreciated.

In sql server also you can do this,

Code Snippet

Create Table Emp

(

EmpId int,

DeptName varchar(10)

)

Insert Into Emp Values(1,'Sales')

Insert Into Emp Values(2,'Sales')

Insert Into Emp Values(3,'Sales')

Insert Into Emp Values(4,'Finance')

Insert Into Emp Values(5,'IT')

Code Snippet

--SQL Server 2005

Declare @.SQL as Varchar(max);

Declare @.PreparedSQL as Varchar(8000);

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?];

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Set @.SQL=''

Select @.SQL = @.SQL + Replace(@.PreparedSQL,'?',deptname) From

(

Select Distinct Deptname from Emp

) as Depts

Exec (@.SQL);

Code Snippet

--SQL Server 2000

Declare @.Depts table (RowId int Identity(1,1), Deptname varchar(10));

Declare @.SQL as Varchar(8000);

Declare @.PreparedSQL as Varchar(8000);

Declare @.I as int;

Set @.PreparedSQL=

'If exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[kl_?]'') and OBJECTPROPERTY(id, N''IsTable'') = 1)

Drop Table [dbo].[kl_?]

Select * Into [kl_?] From Emp Where Deptname=''?'';'

Insert into @.Depts

Select Distinct Deptname From Emp;

Select @.I = 1;

While Exists(Select 1 From @.Depts Where RowId=@.I)

Begin

Select @.SQL = Replace(@.PreparedSQL,'?',deptname)

From @.Depts

Where Rowid=@.I;

--Print(@.SQL);

Exec(@.SQL)

Set @.I=@.I+1;

End

You can utilize the Indexed Views also. See Indexed views on Books Online.

|||Absolute star, thanks very much. Problem solved.