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;
/
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.
Ayhan Yerli wrote: