stored procedure to insert data from 1 table into 4 associated tables

[Madmartigan]

Hi

I have a question from college that requires me to write a script that will copy data from an existing table into 4 related tables. The related tables have been created from normalisation. The values that exist in the original table need to be copied across into their respective tables' associated columns.

Here is the original table and value value insertion script :

[code=mysql]
CREATE TABLE INVOICEDETAILSE XAM(
InvoiceID INT,
InvoiceDate SMALLDATETIME NOT NULL
DEFAULT GETDATE(),
cUSTOMERID INT,
CUSTOMERNAME VARCHAR(50),
CUSTOMERADDRESS VARCHAR (40),
CUSTOMERCITY CHAR(20),
PARTID INT,
PARTDES VARCHAR(35),
QTY INT,
UNTIPRICE SMALLMONEY
)
GO


INSERT INTO INVOICEDETAILSE XAM
VALUES(1, DEFAULT, 1, 'LANNON BUSSI', '12 STAPLEFORD ROAD', 'LONDON', 3, 'SPARKPLUGS', 4, 25.30)
GO

INSERT INTO INVOICEDETAILSE XAM
VALUES(2, DEFAULT, 2, 'CLELIA LOUW', '24 MAIN ROAD STANDERTON', 'DURBAN', 1, 'FANBELT', 3, 12.00)
GO

INSERT INTO INVOICEDETAILSE XAM
VALUES(3, DEFAULT, 3, 'DAVID DE FREITAS', '24 LONSDALE AVENUE', 'CAPE TOWN', 12, 'EXHAUSTS', 4, 5.00)
GO

INSERT INTO INVOICEDETAILSE XAM
VALUES(4, DEFAULT, 1, 'LANNON BUSSI', '12 STAPLEFORD ROAD', 'LONDON', 6, 'NOBBLIES', 65, 350)
GO
[/code]

Here is the script that creates the 4 tables from normalisation :

[code=mysql]
CREATE TABLE CUSTOMERS(
CUSTOMERID INT NOT NULL
PRIMARY KEY,
CUSTOMERNAME VARCHAR(50) NOT NULL,
CUSTOMERADDRESS VARCHAR(40) NOT NULL,
CUSTOMERCITY CHAR(20)
)
GO

CREATE TABLE INVOICES(
INVOICEID INT NOT NULL
PRIMARY KEY,
INVOICEDATE SMALLDATETIME NOT NULL,
CUSTOMERID INT NOT NULL
REFERENCES CUSTOMERS(CUSTO MERID)
)
GO

CREATE TABLE PARTS(
PARTID INT NOT NULL
PRIMARY KEY,
PARTDESC VARCHAR(35),
UNITPRICE SMALLMONEY NOT NULL
)
GO

CREATE TABLE INVOICEDETAILS(
PARTID INT NOT NULL
REFERENCES PARTS(PARTID),
INVOICEID INT NOT NULL
REFERENCES INVOICES(INVOIC EID),
QUANTITY INT NOT NULL
)
GO
[/code]

Any help as to how I go about this would be appreciated.

Thanks

Lannon

[debasisdas]

Can you kindly post the code of the procedure that you have tried.