34,588
社区成员
发帖
与我相关
我的任务
分享
INSERT INTO [t_OrderDetail] ([FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber])
(SELECT a.[FileName],a.[StoreCode],a.[IsVender],a.[IsUrgence],a.[Type],a.[ReceiveDate],a.[JDE_Code],a.[MaterialName],a.[EnUnit],a.[CnUnit],a.[OrderNumber],a.[OriginalNumber],a.[IncreaseNumber],a.[DecreaseNumber]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\OrderData.mdb"')...[t_OrderDetail] a inner join (SELECT distinct [filename],[receivedate],[JDE_Code] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\OrderData.mdb"')...[t_OrderDetail] WHERE [filename] <>'' and [receivedate] is not null and [JDE_Code] <>''
) b on a.[filename] = b.[filename] and a.[receivedate]= b.[receivedate] and a.[JDE_Code]= b.[JDE_Code] )
select identity(int,1,1) as autoID, * into #t_OrderDetail_Tmp1 from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\OrderData.mdb"')...[t_OrderDetail]
select min(autoID) as autoID into #t_OrderDetail_Tmp2 from #t_OrderDetail_Tmp1 group by [filename],[receivedate],[JDE_Code]
INSERT INTO [t_OrderDetail] ([FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber])
SELECT [FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber] from #t_OrderDetail_Tmp1 where autoID in(select autoID from #t_OrderDetail_tmp2)
if exists (select * from #t_OrderDetail_Tmp1)
drop table #t_OrderDetail_Tmp1
if exists (select * from #t_OrderDetail_Tmp2)
drop table #t_OrderDetail_Tmp2
--用过程来写吧,一条语句太复杂
--制造临时表,并生成自动排序列
select identity(int,1,1) as guid,* into #tab from t_OrderDetail where 1 = 2
--插入数据到临时表
INSERT INTO #tab ([FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber])
(SELECT a.[FileName],a.[StoreCode],a.[IsVender],a.[IsUrgence],a.[Type],a.[ReceiveDate],a.[JDE_Code],a.[MaterialName],a.[EnUnit],a.[CnUnit],a.[OrderNumber],a.[OriginalNumber],a.[IncreaseNumber],a.[DecreaseNumber]
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\OrderData.mdb"')...[t_OrderDetail] a inner join (SELECT distinct [filename],[receivedate],[JDE_Code] FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="D:\OrderData.mdb"')...[t_OrderDetail] WHERE [filename] <>'' and [receivedate] is not null and [JDE_Code] <>''
) b on a.[filename] = b.[filename] and a.[receivedate]= b.[receivedate] and a.[JDE_Code]= b.[JDE_Code] )
--插入数据到目标表,并取主键重复的纪录中,排序列最大的纪录
INSERT INTO [t_OrderDetail] ([FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber])
select [FileName],[StoreCode],[IsVender],[IsUrgence],[Type],[ReceiveDate],[JDE_Code],[MaterialName],[EnUnit],[CnUnit],[OrderNumber],[OriginalNumber],[IncreaseNumber],[DecreaseNumber]
from #tab a
where not exists(select 1 from #tab where [filename] =a.[filename] and receivedate= a.receivedate and JDE_Code = a.JDE_Code and guid > a.guid)