try: INSERT ORDERSUB( SYSTEMNO,FLAG,TYPEFLAG,UNIQUENO,OBILLNO,PmcBillNO,RefBillNo,BILLDATE,SEQNO,CUSTID,PRODUCTID,PRODCNAME,MAPPINGNO,QUANTITY,PRODSTRUCTURE,UNIT,PRICE,SUBAMOUNT,CURRENCYID,CURRRATE,ISNEEDMADE,ISGIFTPROD,ONBOARDDATE,CUSTMEMO,WAREID,PRICERATE) SELECT 3,2,2,1,'HC0980313002',DOCID,DOCID,'2009/3/13',1,'LS',P.ProductID,P.Prodcname,FRMRLEPRDID,PRDQTY,P.Prodstructure,P.CALCUNIT,UNTPRC,EXTPRC,'TWD',1,'N','N',SHPDTE,DOCID+'/'+CAST(1 AS VARCHAR(4)),'A',100 FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Program Files\pmc\exe\clientexe\INTERFACE.mdb;User ID=Admin;Password=''')...DPURORDREQ A Left join ComProductMapping M on A.Frmrleprdid=M.MappingNO and M.CustID='LS' Left join ComProduct P on M.ProductID=P.ProductID where docid='4520825112'
set rowcount 0SELECT 3,2,2,1,'HC0980313002',DOCID,DOCID,'2009/3/13',1,'LS',P.ProductID,P.Prodcname,FRMRLEPRDID,PRDQTY,P.Prodstructure,P.CALCUNIT,UNTPRC,EXTPRC,'TWD',1,'N','N',SHPDTE,DOCID+'/'+CAST(1 AS VARCHAR(4)),'A',100 into #table FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Program Files\pmc\exe\clientexe\INTERFACE.mdb;User ID=Admin;Password=''') 这个语句先执行,执行结果放入一个临时表剩下的语句INSERT ORDERSUB( SYSTEMNO,FLAG,TYPEFLAG,UNIQUENO,OBILLNO,PmcBillNO,RefBillNo,BILLDATE,SEQNO,CUSTID,PRODUCTID,PRODCNAME,MAPPINGNO,QUANTITY,PRODSTRUCTURE,UNIT,PRICE,SUBAMOUNT,CURRENCYID,CURRRATE,ISNEEDMADE,ISGIFTPROD,ONBOARDDATE,CUSTMEMO,WAREID,PRICERATE) SELECT 3,2,2,1,'HC0980313002',DOCID,DOCID,'2009/3/13',1,'LS',P.ProductID,P.Prodcname,FRMRLEPRDID,PRDQTY,P.Prodstructure,P.CALCUNIT,UNTPRC,EXTPRC,'TWD',1,'N','N',SHPDTE,DOCID+'/'+CAST(1 AS VARCHAR(4)),'A',100 FROM #临时表 A Left join ComProductMapping M on A.Frmrleprdid=M.MappingNO and M.CustID='LS' Left join ComProduct P on M.ProductID=P.ProductID where docid='4520825112' 试试吧 估计能行
INSERT ORDERSUB(
SYSTEMNO,FLAG,TYPEFLAG,UNIQUENO,OBILLNO,PmcBillNO,RefBillNo,BILLDATE,SEQNO,CUSTID,PRODUCTID,PRODCNAME,MAPPINGNO,QUANTITY,PRODSTRUCTURE,UNIT,PRICE,SUBAMOUNT,CURRENCYID,CURRRATE,ISNEEDMADE,ISGIFTPROD,ONBOARDDATE,CUSTMEMO,WAREID,PRICERATE) SELECT 3,2,2,1,'HC0980313002',DOCID,DOCID,'2009/3/13',1,'LS',P.ProductID,P.Prodcname,FRMRLEPRDID,PRDQTY,P.Prodstructure,P.CALCUNIT,UNTPRC,EXTPRC,'TWD',1,'N','N',SHPDTE,DOCID+'/'+CAST(1 AS VARCHAR(4)),'A',100
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Program Files\pmc\exe\clientexe\INTERFACE.mdb;User ID=Admin;Password=''')...DPURORDREQ A
Left join ComProductMapping M on A.Frmrleprdid=M.MappingNO and M.CustID='LS'
Left join ComProduct P on M.ProductID=P.ProductID where docid='4520825112'
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\Program Files\pmc\exe\clientexe\INTERFACE.mdb;User ID=Admin;Password=''')
这个语句先执行,执行结果放入一个临时表剩下的语句INSERT ORDERSUB(
SYSTEMNO,FLAG,TYPEFLAG,UNIQUENO,OBILLNO,PmcBillNO,RefBillNo,BILLDATE,SEQNO,CUSTID,PRODUCTID,PRODCNAME,MAPPINGNO,QUANTITY,PRODSTRUCTURE,UNIT,PRICE,SUBAMOUNT,CURRENCYID,CURRRATE,ISNEEDMADE,ISGIFTPROD,ONBOARDDATE,CUSTMEMO,WAREID,PRICERATE) SELECT 3,2,2,1,'HC0980313002',DOCID,DOCID,'2009/3/13',1,'LS',P.ProductID,P.Prodcname,FRMRLEPRDID,PRDQTY,P.Prodstructure,P.CALCUNIT,UNTPRC,EXTPRC,'TWD',1,'N','N',SHPDTE,DOCID+'/'+CAST(1 AS VARCHAR(4)),'A',100
FROM #临时表 A
Left join ComProductMapping M on A.Frmrleprdid=M.MappingNO and M.CustID='LS'
Left join ComProduct P on M.ProductID=P.ProductID where docid='4520825112' 试试吧 估计能行