Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Inserting unique records from another table

I have a table called trackingInfo with a unique constraint of the two fields:
Tracking, Ship_Date.

I also have a table called trackingInfo_scratch with the same fields but without any contraints (duplicates are allowed)

I want to insert records into trackingInfo from trackingInfo_scratch, but only the first unique combination of Tracking and Ship_Date.

In my query below I still get the standard contraint error in SQL Server. What am I doing wrong?
INSERT INTO trackingInfo(
  trackingInfo.Shipper,
  trackingInfo.Main_Acct,
  trackingInfo.Tracking,
  trackingInfo.Pickup_Date,
  trackingInfo.Ship_Date)
SELECT
  trackingInfo_scratch.Shipper,
  trackingInfo_scratch.Main_Acct,
  trackingInfo_scratch.Tracking,
  trackingInfo_scratch.Pickup_Date,
  trackingInfo_scratch.Ship_Date,
FROM       trackingInfo_scratch
 WHERE NOT EXISTS
     (SELECT trackingInfo.Tracking, trackingInfo.Ship_Date
           FROM trackingInfo
           WHERE trackingInfo.Tracking = trackingInfo_scratch.Tracking and
                 trackingInfo.Ship_Date = trackingInfo_scratch.Ship_Date)

Open in new window

0
Terryvpd
Asked:
Terryvpd
1 Solution
 
K VDatabase ConsultantCommented:
use distinct keyword.

INSERT INTO trackingInfo(
  trackingInfo.Shipper,
  trackingInfo.Main_Acct,
  trackingInfo.Tracking,
  trackingInfo.Pickup_Date,
  trackingInfo.Ship_Date)
SELECT distinct
  trackingInfo_scratch.Shipper,
  trackingInfo_scratch.Main_Acct,
  trackingInfo_scratch.Tracking,
  trackingInfo_scratch.Pickup_Date,
  trackingInfo_scratch.Ship_Date,
FROM       trackingInfo_scratch
 WHERE NOT EXISTS
     (SELECT trackingInfo.Tracking, trackingInfo.Ship_Date
           FROM trackingInfo
           WHERE trackingInfo.Tracking = trackingInfo_scratch.Tracking and
                 trackingInfo.Ship_Date = trackingInfo_scratch.Ship_Date)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now