can I create a table form existing table using stored procedure so that the new table have new primary key?

Question

   Staff_Id Course_code	semester	_year	Eval_no	Count_of _good	Count_of_poor	Count_of…..                    
    2020/g  Comp201	           1	    2009	  1	        0             	  2	             1                    
    **2020/g    Comp201	         1	      2009**	2	      3	                2	           1                    
    **2020/g    Comp201	         1	      2009**	3	      1	                2	           1                    
    **2020/g    Comp201	         1	      2009**	4	      0	                2	           1                    
    2020/g  Comp211	           1	    2009	  1	        2	              3	             4                    
    **2020/g    Comp211	         1	      2009**	2	      0	                2	           1                    
    **2020/g    Comp211	         1	      2009**	3	      5	                6	           1                    
    **2020/g    Comp211	         1	      2009**	4	      3                 5	           1

First of all thank you very much for your very important help.I need to break the above table into two using a stored procedure so that the first four columns become tableA and the entire table become tableB.Then I need to create relation ship between tableA and tableB.The first four fields of the entire table are primary key of tableA and foreign key of tableB. I have less knowledge of stored procedures so help me please! The reason is that the first four fields of my table are repeated and I need to eliminate that. I couldn't design in the way I need b/c the information is collected from webform and stored that way in my database. I have to design a report that shows information about staff_Id,couse_code,semester,year and then counts of his/her performance for each eval_no

Of course my question is long and might be confusing if it is understandable to you please help on how can i program my sql2000 server to do that.

Answer 1

You wouldn't use a stored procedure, necessarily for this. A stored procedure is like a function or method in front end programming. You use it for repeatable items. For a one time thing, you'd create two new tables.

create table TableA (            
  StaffID varchar(6)            
, Course_code varchar(20)            
, semester tinyint            
, _year int            
)             
go            
create table TableB (            
  StaffID varchar(6)            
, Course_code varchar(20)            
, semester tinyint            
, _year int            
, Eval_no int            
...              
)            
go

Then you could move the data. However, rather than make the first four columns the PK, I'd use a surrogate like this:

CREATE TABLE [dbo].[TableA](            
    [TableAID] [int] IDENTITY(1,1) NOT NULL,            
    [StaffID] [varchar](6) NULL,            
    [Course_code] [varchar](20) NULL,            
    [semester] [tinyint] NULL,            
    [_year] [int] NULL,            
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED             
(            
    [TableAID] ASC            
)            
) ON [PRIMARY]

go create table TableB ( TableBID int identity(1,1 ) , TableAID int , Course_code varchar(20) , semester tinyint , _year int , Eval_no int ) CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ( [TableBID] ASC ) ) ON [PRIMARY] go ALTER TABLE [dbo].[TableB] WITH CHECK ADD CONSTRAINT [FK_TableB_TableA] FOREIGN KEY([TableAID]) REFERENCES [dbo].[TableA] ([TableAID]) GO

ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [FK_TableB_TableA] GO

This will give you two tables that are related. When you insert, ignore the TableAID and TableBID columns.

Answer 2

Something like

select distinct identity(int, 1, 1) as rowid, Staff_Id, Course_code, semester, _year into TableA from MyTable

alter tale TableA add constraint pk_TableA primary key (rowid) create unique index ix on TableA (rowid, Staff_Id, Course_code, semester, _year)

select TableA_rowid = rowid, Eval_no,Count_of,_good
into TableB from mytable tm join TableA ta on tm.Staff_Id = ta.Staff_Id tm.Course_code = ta.Course_code tm.semester = ta.semester tm._year = ta._year

alter table TableB add constraint fk_a foreign key (TableA_rowid) references TableB (rowid)

Answer 3

0

You need to add the primary key and any other indexes manually after the table is created. Using the SELECT INTO doesn't include any indexes, keys or constraints.

A word of warning on SQL 2000 is that your system objects will be locked for the length of the SELECT INTO command so you can't create other objects in the database while that SELECT INTO command is running. SQL 2005 and up are better about this.

answered Oct 09 '09 at 02:59 PM

mrdenny
897 ● 3

(comments are locked)

can I create a table form existing table using stored procedure so that the new table have new primary key?

Follow this question

Related Questions