Re: How to use T-SQL to copy data between tables

I have never done this, but I think you may want to read up on UNION
queries, and use the format of

INSERT INTO sales_invoice VALUES
(
     SELECT {COLUMNS} from sales_order_details
  UNION ALL
     SELECT {COLUMNS} from sales_order_details
)

On 11/14/06, ascll <[email protected]> wrote:
>
> 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]
>
>


-- 
Brandon Schenz
www.schenzcustomdesigns.com