CODEWITHSUNDEEP
sql
Mangesh
Mangesh

Reputation: 43

Decompose(Normalize) 1 table into 2 tables

I am having following table with near about 25000 records.

Table Namee: tblStdCods
Table Columns: id, Place, STD Code, State

Is there any way to decompose table into two like this WITHOUT AFFECTING DATA STORED IN IT,

tblStdCodes: id, Place, stadcode, stateId
tblState: stateId, stateName 

Original Table :

id    Place        STDCode   State
1     Ahmednagar    414003   Maharashtra

Resultanat Tables :

table 1: 
id    Place        STDCode   State
1     Ahmednagar    414003   1

table 2:

stateId  stateName
1        Maharashtra

Upvotes: 1

Views: 1968

Answers (1)

aleroot
aleroot

Reputation: 72676

You have to create two tables with the structure you desire and after that you can simply Insert data from the old table, like this : 

//tblState have the field stateName of type int AUTO_INCREMENT
INSERT INTO tblState (stateName) SELECT State FROM tblStdCods GROUP BY State;  

//Insert records with the pointer to the state table
INSERT INTO tblStdCodes (Id,Place, stadcode, stateId) SELECT Id, Place, STD Code, stateID FROM 
tblStdCods JOIN tblState ON State = stateName

The fields type of the two resultant tables will be derived from the original table, the only thing to be careful about is to have the AUTO_INCREMENT on the stateID field of the tblState table, in this way the database will generate automatically for you id's ...

Upvotes: 2

Related Questions