Using SQL to Divide a Table into Multiple Tables

The PRIMARY KEY CLUSTERED ENGINE ASC can be found in [PK_Engine]. If you have a column to partition the table, there are different solutions available. One solution is to run a specific command. Another solution involves normalizing the tables by creating new ones, writing SQL to transfer data to the new tables, and then deleting the original table. Additionally, if you use the partitioning method, you will get a predictable number of rows, but the size of the rows may vary depending on the data in each partition. Therefore, the tables may not be of equal sizes.

Question:

Hi I have this
table Cars
:

MODEL nvarchar(20)
STYLE nvarchar(20)
ENGINE nvarchar(5)
CAPACITY smallint
MAX_SPEED smallint
PRICE smallmoney
MARKET nvarchar(20)
COMPETITOR nvarchar(20)

I aim to divide it using an SQL query into three tables.

Cars:

MODEL nvarchar(20)
STYLE nvarchar(20)
MAX_SPEED smallint
PRICE smallmoney

Engine:

ENGINE nvarchar(5)
CAPACITY smallint

Market:

MARKET nvarchar(20)
COMPETITOR nvarchar(20)

I am curious about the way to accomplish this task using SQL commands. Thank you.


Solution 1:

The most effortless approach is to choose “Select… Into”, which will generate fresh tables.

SELECT DISTINCT
    ENGINE,
    CAPACITY
INTO Engine
FROM CARS
SELECT DISTINCT
    MARKET,
    COMPETITOR
INTO Market
FROM CARS

One way to achieve this is by removing the obsolete columns directly from the initial table. For example, you can do this by dropping them.

ALTER TABLE Cars DROP COLUMN ENGINE
ALTER TABLE Cars DROP COLUMN CAPACITY
ALTER TABLE Cars DROP COLUMN MARKET
ALTER TABLE Cars DROP COLUMN COMPETITOR

The solution provided addresses your specific request, but it may not align with your intentions as it omits any mention of the car’s reference to the engine or market details, resulting in a loss of information.

My suggestion would be to keep the columns “ENGINE” and “MARKET” as foreign keys on the car table if they define the keys of the new table. Therefore, only the columns “Capacity” and “Competitor” should be dropped.

Consider creating the primary key on the newly created tables as well. For instance, you can use the following syntax: ALTER
TABLE ENGINE
ADD CONSTRAINT [PK_Engine] PRIMARY KEY CLUSTERED ENGINE ASC.


Solution 2:

Run this….

create table Engine
(
    EngineId int identity(1,1) not null primary key,
    Engine nvarchar(5) not null,
    Capacity smallint not null
)
go
insert into Engine
(Engine, Capacity)
(select distinct Engine,Capacity from Cars)
go
alter table Cars
add EngineId int null
go
update Cars
set Cars.EngineId = e.EngineId
from Engine e where  e.Engine = Cars.Engine
go
create table Market
(
    Id int identity(1,1) not null primary key,
    Market nvarchar(20) not null,
    Competitor nvarchar(20) not null
)
go
insert into Market
(Market, Competitor)
(select distinct Market,Competitor from Cars)
go
alter table Cars
add MarketId int null
go
update Cars
set Cars.MarketId = m.MarketId
from Market m where  m.Market = Cars.Market
go
alter table Cars
drop column Market;
alter table Cars
drop column Competitor;
alter table Cars
drop column Engine;
alter table Cars
drop column Capacity;


Solution 3:


Before you can standardize the tables, you must initially generate new tables using

create

and then use SQL to transfer the data into the new tables by utilizing

insert

. Finally, you can discard the original table with

alter

.

Check out the following links for more information: http://technet.microsoft.com/en-us/library/ms174979.aspx, http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx, and http://msdn.microsoft.com/en-us/library/ms190273.aspx.

Frequently Asked Questions

Posted in Sql