x

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

   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.

more ▼

asked Oct 09 '09 at 05:15 AM in Default

Dereje gravatar image

Dereje
22 2 2 2

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered Oct 09 '09 at 11:53 AM

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 76 79 82

(comments are locked)
10|1200 characters needed characters left

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)

more ▼

answered Oct 09 '09 at 12:54 PM

nigelrivett gravatar image

nigelrivett
92

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 09 '09 at 02:59 PM

mrdenny gravatar image

mrdenny
908 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x472
x19

asked: Oct 09 '09 at 05:15 AM

Seen: 7600 times

Last Updated: Oct 09 '09 at 08:56 AM