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/win32?unsub=gcdmw-win32@m.gmane.org

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

------=_Part_4202_4730210.1163510805012
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 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/win32?unsub=brandons@midwestsports.co m
>
>


--
Brandon Schenz
www.schenzcustomdesigns.com

------=_Part_4202_4730210.1163510805012--

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

You are trying to do a cross tab function. This could give you all
sorts of problems if your orders have more than ten products.

I Assume that you can only have one invoice per order.

First job is to validate the data and resolve any issues there.

If this is a one off I would drop it into something like Excel that has
cross tab capability (pivot tables) then, if you must, load it into your
table from there.

If you need to repeat this frequently you need to write a stored
procedure that will read your two existing tables joining on order
number and sorted to invoice number. You can then construct each row
for your new table on change of order number.

Good luck


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org