Assignment: Enhance a Database

Task 1: Create tables.

Create two new tables (tblWorkOrder and tblWorkOrderLine) in the database. The two new created tables are as follows:

CREATE TABLE tblWorkOrder

(WorkOrderID int PRIMARY KEY,

ProductID char(5) references tblproduct(ProductID),

WOQuantity Decimal (8,2),

WOEmpID char(6) references tblEmployee(EmpID),

WODateIssued DATETIME,

WODateNeeded DATETIME);

CREATE TABLE tblWorkOrderLine

(WorkOrderLineID int PRIMARY KEY,

WorkOrderID int references tblWorkOrder(WorkOrderID),

ProductID char(5) references tblproduct(ProductID),

WOLineQuantity Decimal (8,2)

);

Task 2: Enhance the product table and populate the new tables

The worksheet named WOAllData contains all the data that would be in a cross join of the WorkOrder and WorkOrderLine tables. This is a two-step import process . first create a table (WOAllData) to store the data on the worksheet, use the Import Wizard to import the data from the Excel workbook into WOAllData, and then use SQL to place the data in the correct tables. Use the INSERT INTO/SELECT FROM syntax to take the data from the WOAllData table and place it into the two new tables (WorkOrder and WorkOrderLine) that were created in Task 1.

Questions 1: Write a SQL code Use the INSERT INTO/SELECT FROM syntax to take the data from the WOAllData table and place it into the two new tables (WorkOrder and WorkOrderLine) .

My SQL code was as follows: INSERT INTO tblworkorder (WorkOrderID, ProductID , WOQuantity , WOEmpID , WODateIssued , WODateNeeded )

SELECT WorkOrderID , ProductID , WOQuantity , WOEmpID , WODateIssued , WODateNeeded FROM tblWOAllData; but results an error message stating Cannot insert duplicate key in object 'dbo.tblWorkOrder'. The duplicate key value is (1). Please write a Correct SQL code which helps to populate the data to the above two tables.