How to use T-SQL to copy data between tables

Greetings,

I having problem to copy data from one table (in
master-detail) to another table. Totally 3 tables
involved and below are their structures: -

# Master table and I able to get the 'InvoiceNo' from
other table
CREATE TABLE `sales_order` (
  `OrderNo` int(8) unsigned AUTO_INCREMENT,

  `InvoiceNo` int(8) unsigned default '0',

  PRIMARY KEY (`OrderNo`)
) ENGINE=MyISAM;



# Detail table and a single order can have 1 or more
items (Max. 10 and Item_No == auto increase)
CREATE TABLE `sales_order_detail` (
  `OrderNo` int(8) unsigned NOT NULL,
  `Item_No` int(2) unsigned AUTO_INCREMENT,

  `ProductID` varchar(31) default NULL,

  PRIMARY KEY (`OrderNo`,`Item_No`)
) ENGINE=MyISAM;



CREATE TABLE `sales_invoice` (
  `InvoiceNo` int(8) unsigned NOT NULL default '0',

  `ProductID01` varchar(31) default NULL,
  `ProductID02` varchar(31) default NULL,
  `ProductID03` varchar(31) default NULL,
  `ProductID04` varchar(31) default NULL,
  `ProductID05` varchar(31) default NULL,
  `ProductID06` varchar(31) default NULL,
  `ProductID07` varchar(31) default NULL,
  `ProductID08` varchar(31) default NULL,
  `ProductID09` varchar(31) default NULL,
  `ProductID10` varchar(31) default NULL,

  PRIMARY KEY (`InvoiceNo`)
) ENGINE=MyISAM;


Question
========
How do I copy data from "sales_order_detail" to
"sales_invoice" if I want do: -
 - copy all 'ProductID' for particular 'OrderNo' in
"sales_order_detail" to "sales_invoice". For example,
if 'OrderNo'=1 and it have 3 'ProductID', I need to
copy 'OrderNo'=1's 'Item_No'=1 to
sales_invoice.ProductID01, 'Item_No'=2 to
sales_invoice.ProductID02 and so forth.

 - After 'OrderNo'=1, actually I need to copy ALL
records in "sales_order_detail" to "sales_invoice" in
manner above


Any help is highly appreciated.

Thanks in advance and I'm using MySQL ver 5.0.27 on
Windows 2000.


ascll


 
____________________________________________________________________________________
Cheap talk?
Check out Yahoo! Messenger's low PC-to-Phone call rates.
http://voice.yahoo.com

-- 
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe:    http://lists.mysql.com/[email protected]