CODEWITHSUNDEEP
sql-servert-sql
Joe
Joe

Reputation: 101

How can i check for duplicates before using 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 #]

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

Upvotes: 2

Views: 130

Answers (4)

gotqn
gotqn

Reputation: 43626

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

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

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 #]
)

Upvotes: 0

HABO
HABO

Reputation: 15816

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 ... )

Upvotes: 2

David A
David A

Reputation: 394

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.

Upvotes: 1

Related Questions