CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Chad Patrick
Chad Patrick

Reputation: 251

Copy Data from multiple table to one table without duplicate

I want to insert data from Source (1&2) tables to Destination table without duplicates

Table: Source

       SONGs       ARTISTs         ALBUM s
       Baby         John          RocknRoll
       Moon         Mike          Moonlight
       Firefly      Chad           Garden

Table: Source2

       SONGs       ARTISTs         ALBUMs
       Happy        Jane           Fresh
       Baby         John          RocknRoll
       Bday         Zelda           Link

Table: Destination

ID     SONG       ARTIST         ALBUM       CATALOG#     TRACK#   <--- Columns
 1     baby        John        RocknRoll

Here is my code:

INSERT INTO Destination
     (SONG,
     ARTIST,
     ALBUM)

SELECT DISTINCT
     So.SONGs,
     So.ARTISTs,
     So.ALBUMs,
     FROM Source So

LEFT JOIN Destination Ds
     ON Ds.SONG= So.SONGs
     AND Ds.ARTIST= So.ARTISTs
     AND Ds.ALBUM= So.ALBUMs

WHERE
     Ds.SONG IS NULL
 AND Ds.SONG IS NULL
 AND Ds.SONGIS NULL

However after trying this code on both table i still get a duplicate

I Tried UNION but I have more than 2 source table?

Upvotes: 2

Views: 58

Answers (3)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can do UNION istead of JOIN

INSERT INTO Destination
     (SONG,
     ARTIST,
     ALBUM)

SELECT 
     So.SONGs,
     So.ARTISTs,
     So.ALBUMs
FROM (
     SELECT So.SONGs,
            So.ARTISTs,
            So.ALBUMs
     FROM Source

     UNION

     SELECT So.SONGs,
            So.ARTISTs,
            So.ALBUMs
     FROM Source2

     UNION

     SELECT So.SONGs,
            So.ARTISTs,
            So.ALBUMs
     FROM Source3

     UNION

     SELECT So.SONGs,
            So.ARTISTs,
            So.ALBUMs
     FROM Source4

     UNION

     SELECT So.SONGs,
            So.ARTISTs,
            So.ALBUMs
     FROM Source5

     )
WHERE
     Ds.SONG IS NULL
 AND Ds.SONG IS NULL
 AND Ds.SONGIS NULL

Upvotes: 2

Panther
Panther

Reputation: 3339

  select SONGs , ARTISTS , ALBUMS
  from Source 

   Union

   select SONGs , ARTISTS , ALBUMS
   from Source2

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use UNION to remove duplicates and add an additional NOT EXISTS clause:

INSERT INTO Destination(SONG, ARTIST, ALBUM)
    SELECT * 
    FROM(
        SELECT SONGs, ARTISTs, ALBUMs
        FROM Source
        UNION 
        SELECT SONGs, ARTISTs, ALBUMs
        FROM Source2
    )t
    WHERE
        NOT EXISTS(
            SELECT 1
            FROM DESTINATION
            WHERE
                SONG = t.SONGs
                AND ARTIST = t.ARTISTs
                AND ALBUM = t.ALBUMs
        )

Upvotes: 0

Related Questions