如何在使用insert from select之前检查副本

I have the following code to get data from linked server but how can i check whether there is a duplicate record or not in sqlserver table if there is skip for Next otherwise insert from select. i want to compare using the following filed [CUSTOMER],[SO DATE],[SO NUMBER],[itemid],[PROMISED DATE],[PO #]

我有以下代码从链接服务器获取数据，但是如何检查sqlserver表中是否有重复记录，如果下一个表中有跳转，否则将从select中插入。我想比较一下使用以下文件[客户]，[日期]，[数字]，[itemid]，[承诺日期]，[PO #]

insert db1.dbo.WORKORDERS 
   ([WORK ORDER #],[QUANTITY],[CUSTOMER],[SO DATE],[SO NUMBER],[ROUTING],[itemid],[CUSTOMER PN],[SHIP VIA],[PROMISED DATE],[COMMENTS],[PO #],[WO Notes])
select 
  convert(varchar(10), next value for dbo.WorkOrderSequence ),
x.Quantity,--
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference,
  X.Category,
  x.ItemID,
  x.PartNumber,
  x.WhichShipVia,
  x.ShipByDate,
  x.Comment2,
  x.CustomerInvoiceNo,
  x.SalesDescription
from
  openquery
  ([DB29],
    'select              
        Customers.Customer_Bill_Name,
        JrnlHdr.TransactionDate,
       Cast(JrnlRow.Quantity as Decimal(38,0)) as Quantity,
        JrnlHdr.Reference, 
        LineItem.Category,
       LineItem.ItemID ,
        LineItem.PartNumber, 
        Customers.WhichShipVia, 
        JrnlHdr.ShipByDate, 
        JrnlHdr.Comment2,
        JrnlHdr.CustomerInvoiceNo, 
        LineItem.SalesDescription    
FROM   Customers
    INNER JOIN JrnlHdr
ON Customers.CustomerRecordNumber = JrnlHdr.CustVendId
    LEFT OUTER JOIN Address
ON Customers.CustomerRecordNumber = Address.CustomerRecordNumber
    INNER JOIN JrnlRow
ON JrnlHdr.PostOrder = JrnlRow.PostOrder
    INNER JOIN LineItem 
ON JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber
WHERE  JrnlHdr.JrnlKey_Journal = 11 AND JrnlHdr.TransactionDate = CURDATE()
AND JrnlHdr.PostOrder = JrnlRow.PostOrder  
AND JrnlHdr.CustVendId = Customers.CustomerRecordNumber 
AND JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber 
AND JrnlHdr.POSOisClosed = 0

)  as x

4 个解决方案

#1

Since you are to use insert only so Not exists is the ideal choice and not much change is require.

因为您要使用插入，所以不存在是理想的选择，不需要太多的更改。

try to fix error of your own,

试着修复你自己的错误，

insert db1.dbo.WORKORDERS 
   ([WORK ORDER #],[QUANTITY],[CUSTOMER],[SO DATE],[SO NUMBER],[ROUTING],[itemid],[CUSTOMER PN],[SHIP VIA],[PROMISED DATE],[COMMENTS],[PO #],[WO Notes])
Select 
  convert(varchar(10), next value for dbo.WorkOrderSequence ),
  Quantity,
  Customer_Bill_Name,
  TransactionDate,
  Reference,
  Category,
  ItemID,
  PartNumber,
  WhichShipVia,
  ShipByDate,
  Comment2,
  CustomerInvoiceNo,
  SalesDescription 
FROM
(
select 
x.Quantity,--
  x.Customer_Bill_Name,
  x.TransactionDate,
  x.Reference,
  X.Category,
  x.ItemID,
  x.PartNumber,
  x.WhichShipVia,
  x.ShipByDate,
  x.Comment2,
  x.CustomerInvoiceNo,
  x.SalesDescription
from
  openquery
  ([DB29],
    'select              
        Customers.Customer_Bill_Name,
        JrnlHdr.TransactionDate,
       Cast(JrnlRow.Quantity as Decimal(38,0)) as Quantity,
        JrnlHdr.Reference, 
        LineItem.Category,
       LineItem.ItemID ,
        LineItem.PartNumber, 
        Customers.WhichShipVia, 
        JrnlHdr.ShipByDate, 
        JrnlHdr.Comment2,
        JrnlHdr.CustomerInvoiceNo, 
        LineItem.SalesDescription    
FROM   Customers
    INNER JOIN JrnlHdr
ON Customers.CustomerRecordNumber = JrnlHdr.CustVendId
    LEFT OUTER JOIN Address
ON Customers.CustomerRecordNumber = Address.CustomerRecordNumber
    INNER JOIN JrnlRow
ON JrnlHdr.PostOrder = JrnlRow.PostOrder
    INNER JOIN LineItem 
ON JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber
WHERE  JrnlHdr.JrnlKey_Journal = 11 AND JrnlHdr.TransactionDate = CURDATE()
AND JrnlHdr.PostOrder = JrnlRow.PostOrder  
AND JrnlHdr.CustVendId = Customers.CustomerRecordNumber 
AND JrnlRow.ItemRecordNumber = LineItem.ItemRecordNumber 
AND JrnlHdr.POSOisClosed = 0

)  as x'
)t4
where not exists(
select 1 from db1.dbo.WORKORDERS WO where wo.[CUSTOMER]=t4.Customer_Bill_Name
and [SO DATE]=t4.TransactionDate AND[SO NUMBER]=t4.Reference 
AND[itemid]=t4.ItemID and [PROMISED DATE]=t4.ShipByDate and [PO #]=t4.[PO #]
)

#2

Use a correlated subquery with exists to eliminate rows that are already present:

使用存在的关联子查询来消除已经存在的行:

select ... from ... where not exists ( select 42 from db1.dbo.WorkOrders where ... )

选择……从…不存在的地方(从db1.dbo中选择42。WorkOrders……)

#3

You might find that the MERGE statement works for this purpose. It lets you specify the destination table, source query, join criteria, and actions to take on match or no match.

您可能会发现MERGE语句适用于此目的。它允许您指定目标表、源查询、连接条件和在匹配或不匹配时采取的操作。

#4

You can use EXCEPT:

您可以使用除了:

This will give you the records that do not exist on the linked server:

这将为您提供链接服务器上不存在的记录:

SELECT [CUSTOMER],[SO DATE],[SO NUMBER],[itemid],[PROMISED DATE],[PO #]
FROM current_database
EXCEPT
SELECT [CUSTOMER],[SO DATE],[SO NUMBER],[itemid],[PROMISED DATE],[PO #]
FROM linked_server_current_database

This will give you the records that do not exits on the current server:

这将为您提供当前服务器上不存在的记录:

SELECT [CUSTOMER],[SO DATE],[SO NUMBER],[itemid],[PROMISED DATE],[PO #]
FROM  linked_server_current_database
EXCEPT
SELECT [CUSTOMER],[SO DATE],[SO NUMBER],[itemid],[PROMISED DATE],[PO #]
FROM current_database

#1