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.

Create Script

I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RMWhat error did you get exactly?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"RM" <darasingha_nospam@.yahoo.com> wrote in message
news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
I am trying to use a script to create a table that already exists in one DB
in another DB. I used Enterprise Manager, "copied" the table and pasted it
into notepad which gave me the following script. However, I cannot get SQL
query analyzer to accept this script. Not sure what I am doing wrong:
USE MainDB
CREATE TABLE [dbo.ReportSite] (
[ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
[SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY] ,
CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
(
[ReportSiteKey]
) ON [PRIMARY]
) ON [PRIMARY]
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
allAdministrators,allPowerUsers
GO
GRANT SELECT ON dbo.ReportSite TO public
GO
GRANT SELECT ON dbo.ReportSite TO allUsers
GO
Thanks,
RM|||Incorrect syntax - near primary (at the end of the first constraint
statement)
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eDhhMYzAFHA.3336@.TK2MSFTNGP11.phx.gbl...
> What error did you get exactly?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "RM" <darasingha_nospam@.yahoo.com> wrote in message
> news:evsFlOzAFHA.4044@.TK2MSFTNGP10.phx.gbl...
> I am trying to use a script to create a table that already exists in one
DB
> in another DB. I used Enterprise Manager, "copied" the table and pasted
it
> into notepad which gave me the following script. However, I cannot get
SQL
> query analyzer to accept this script. Not sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Hello RM,
In your create script you have your table name as [dbo.ReportSite] which
if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
So change your script to be dbo.[ReportSite] and everything should be ok.
Aaron Weiker
http://aaronweiker.com/

> I am trying to use a script to create a table that already exists in
> one DB in another DB. I used Enterprise Manager, "copied" the table
> and pasted it into notepad which gave me the following script.
> However, I cannot get SQL query analyzer to accept this script. Not
> sure what I am doing wrong:
> USE MainDB
> CREATE TABLE [dbo.ReportSite] (
> [ReportSiteKey] [int] IDENTITY (1, 1) NOT NULL ,
> [SiteURL] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [SitePath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [Description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> CONSTRAINT [PK_ReportSite] PRIMARY KEY NONCLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY] ,
> CONSTRAINT [IX_ReportSite] UNIQUE CLUSTERED
> (
> [ReportSiteKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> GRANT SELECT,INSERT,UPDATE,DELETE ON dbo.ReportSite TO
> allAdministrators,allPowerUsers
> GO
> GRANT SELECT ON dbo.ReportSite TO public
> GO
> GRANT SELECT ON dbo.ReportSite TO allUsers
> GO
> Thanks,
> RM
>|||Thanks - worked.
RM.
"Aaron Weiker" <aaron@.sqlprogrammer.org> wrote in message
news:138326632422751463292620@.news.microsoft.com...
> Hello RM,
> In your create script you have your table name as [dbo.ReportSite] which
> if you run this as dbo your table name will now be dbo.[dbo.ReportSite].
> So change your script to be dbo.[ReportSite] and everything should be ok.
> --
> Aaron Weiker
> http://aaronweiker.com/
>
>sql