CODEWITHSUNDEEP
mysqlsql-server-2005
Andrew Lindemulder
Andrew Lindemulder

Reputation: 191

SQL Server and MySQL

I am trying to take data from an MS SQL server 2005 table and insert it into a linked MySQL table, my query is bellow but it is not working. I keep getting "Query Executed successfully, 0 Rows Affected". Any help would be greatly appreciated!

INSERT INTO OPENQUERY(WEBSITE, 'SELECT SalesID,Cust,OrderDate,PONum,PartNo,QTY,PartDesc,DiscPct,DueDate,ShipCity,ShipSt,OrderStatus,InvoiceNo,InvDate,OrderTotal FROM orders')
SELECT Orders.SalesID, Orders.CustDesc, Orders.DateEnt, Orders.PONum, OrderDet.PartNo, OrderDet.QtyOrdered, OrderDet.PartDesc, OrderDet.DiscPct, OrderDet.DueDate, Orders.ShipCity, Orders.ShipSt, OrderDet.Status, BillingDet.InvoiceNo, Billing.InvDate, Orders.OrderTotal
FROM Orders INNER JOIN OrderDet ON (Orders.OrderNo = OrderDet.OrderNo)
LEFT JOIN BillingDet ON (Orders.PONum = BillingDet.PONum)
LEFT JOIN Billing ON (BillingDET.InvoiceNo = Billing.InvoiceNo)
WHERE OrderDet.PartNo LIKE '%^%' AND Orders.DateEnt >='2011/12/31'

Upvotes: 0

Views: 63

Answers (2)

Sebastian Meine
Sebastian Meine

Reputation: 11773

assuming you want to insert into WEBSITE...orders try this:

INSERT  INTO WEBSITE...orders
        (
          SalesID,
          Cust,
          OrderDate,
          PONum,
          PartNo,
          QTY,
          PartDesc,
          DiscPct,
          DueDate,
          ShipCity,
          ShipSt,
          OrderStatus,
          InvoiceNo,
          InvDate,
          OrderTotal
        )
        SELECT  Orders.SalesID,
                Orders.CustDesc,
                Orders.DateEnt,
                Orders.PONum,
                OrderDet.PartNo,
                OrderDet.QtyOrdered,
                OrderDet.PartDesc,
                OrderDet.DiscPct,
                OrderDet.DueDate,
                Orders.ShipCity,
                Orders.ShipSt,
                OrderDet.Status,
                BillingDet.InvoiceNo,
                Billing.InvDate,
                Orders.OrderTotal
        FROM    Orders
        INNER JOIN OrderDet
                ON ( Orders.OrderNo = OrderDet.OrderNo )
        LEFT JOIN BillingDet
                ON ( Orders.PONum = BillingDet.PONum )
        LEFT JOIN Billing
                ON ( BillingDET.InvoiceNo = Billing.InvoiceNo )
        WHERE   OrderDet.PartNo LIKE '%^%'
                AND Orders.DateEnt >= '2011/12/31'

Upvotes: 1

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

INSERT INTO WEBSITE...orders(SalesID, Cust, OrderDate, PONum, PartNo, QTY, PartDesc, DiscPct, DueDate,
ShipCity, ShipSt, OrderStatus, InvoiceNo, InvDate, OrderTotal)
SELECT O.SalesID, O.CustDesc, O.DateEnt, O.PONum, OrderDet.PartNo,
OrderDet.QtyOrdered, OrderDet.PartDesc, OrderDet.DiscPct, OrderDet.DueDate,
O.ShipCity, O.ShipSt, OD.Status, BD.InvoiceNo, B.InvDate,
O.OrderTotal
FROM Orders O
    INNER JOIN OrderDet OD
        ON (O.OrderNo = OD.OrderNo)
    LEFT JOIN BillingDet BD
        ON (O.PONum = BD.PONum)
    LEFT JOIN Billing B
        ON (BD.InvoiceNo = B.InvoiceNo)
WHERE OD.PartNo LIKE '%^%'
    AND O.DateEnt >='2011/12/31'

Upvotes: 0

Related Questions