Splitting one table into two new tables SQL Server 2008 express 1

[neronikita]

I have one table with the following fields:

recordid
truckno
date
workitemcompleted
completedby
mileage
timestarted
timestopped
complaint_symptoms
code
addlcomments

After I entered over 500 rows, I realized how I should actually have set this up. I want to have one table for the information that doesn't change for every work item completed on that day to that truck. For example...
Table 1
RecordID
TruckNo
Date
Mileage
Timestarted
timestopped
addlcomments

Table 2
RecordID (fk)
workitemcompleted
completed by
complaint_symptoms
Code
comments

or something like that.

My question is, how do I take the existing table and split it into the two new tables without losing all that I have already entered. Also, am I thinking correctly that this is the way I should have done it in the first place?

Thanks in advance for any help.

Di

 

[RiverGuy]

If you're saying that on each day, each truck can have multiple work items, then you do want two tables. Here's how to accomplish that:

1. Create the two new tables

CREATE TABLE DailyVehicleTracking (
DailyVehicleTrackingID INT IDENTITY NOT NULL PRIMARY KEY,
VehicleNo VARCHAR(20) NOT NULL, --use your own data types for these, I am just guessing
TrackingDate DATE NOT NULL,
Mileage INT NOT NULL,
TimeStarted INT NOT NULL,
TimeStopped INT NOT NULL,
AddlComments VARCHAR(512) NULL
)

CREATE TABLE VehicleMaintenanceOperations (
VehicleMaintenanceOperationID INT IDENTITY NOT NULL PRIMARY KEY,
DailyVehicleTrackingID INT FOREIGN KEY REFERENCES DailyVehicleTracking(DailyVehicleTrackingID),
WorkItemCompleted INT NOT NULL,
CompletedBy VARCHAR(100),
ComplaintSymptoms VARCHAR(512),
[Code] VARCHAR(512), 
Comments VARCHAR(512)
)

Then, to insert your existing data into these new tables, do the following:

SET IDENTITY_INSERT DailyVehicleTracking ON
INSERT INTO DailyVehicleTracking (
DailyVehicleTrackingID,
VehicleNo,
TrackingDate,
Mileage,
TimeStarted,
TimeStopped,
AddlComments
)
SELECT
RecordID,
VehicleNo,
TrackingDate,
Mileage,
TimeStarted,
TimeStopped,
AddlComments
FROM ExistingTable

SET IDENTITY_INSERT DailyVehicleTracking OFF

INSERT INTO VehicleMaintenanceOperations
SELECT
RecordID,
WorkItemCompleted,
CompletedBy,
ComplaintSymptoms,
[Code],
Comments
FROM ExistingTable

Then when you are satisfied the new tables are doing the job, drop the existing table with a DROP TABLE TableName command.

 

[neronikita]

Thank you so much for your quick response, I'm going to try that now. I guess I'm a little more rusty than I thought...

Di