-1

I am new in sql server. I have a two tables [FLT_SKDL] where multiple duplicates numbers are there and [SHIP] where primary key is used and want to insert only one ID. And I want to insert data from [FLT_SKDL] to [SHIP] in my [SHIP] table there is primary key is used so I should have to ignore duplicates I used following syntax. 

INSERT  INTO DB01TD02.BRIC.SHIP
SELECT SHIP_NO, SHIP_TYPE_IATA, SHIP_TYPE_DIA, SHIP_TYPE_ICAO, SYSDATETIME(),'EMP', 'BRIC', SYSDATETIME(),'EMP', 'BRIC'
FROM  DB01TD02.BRIC.FLT_SKDL 
WHERE NOT EXISTS(SELECT SHIP_NO FROM DB01TD02.BRIC.SHIP  WHERE DB01TD02.BRIC.SHIP.SHIP_NO = DB01TD02.BRIC.FLT_SKDL.SHIP_NO)
AND DB01TD02.BRIC.FLT_SKDL.SHIP_NO IS NOT NULL

I Tried

Avoid duplicates in INSERT INTO SELECT query in SQL Server

But not worked in my case. Is there any another solution. Thanks in advance

niksan karkee
  • 157
  • 1
  • 17
  • Define "But not worked" – Caius Jard Dec 13 '18 at 08:51
  • @CaiusJard I tried all answer from that link (such as NOT EXISTS, NOT IN, JOIN/IS NULL) – niksan karkee Dec 13 '18 at 08:53
  • 1
    When you ask a question in future, don't just say "but it doesn't work" - say what doesnt work; an error message comes? The ships you want aren't there? Your computer explodes? What? We aren't mind readers, and saying "it doesn't work" doesnt mean anything to us; you have so say WHY it doesnt work by describing what it does do and also what you want it to do and perhaps why the two things are different (if it's not obvious) – Caius Jard Dec 13 '18 at 09:50
  • @CaiusJard Sorry and thank you so much for you suggestion. I will do from next time – niksan karkee Dec 14 '18 at 01:39

1 Answers1

1

I usually do my "insert where not already there" with the left join pattern:

INSERT INTO DB01TD02.BRIC.SHIP (put,a,list,of,columns,here,please)
SELECT f.SHIP_NO, f.SHIP_TYPE_IATA, f.SHIP_TYPE_DIA, f.SHIP_TYPE_ICAO, SYSDATETIME(), 'EMP', 'BRIC', SYSDATETIME(),'EMP', 'BRIC'
FROM  
  DB01TD02.BRIC.FLT_SKDL f
  LEFT JOIN
  DB01TD02.BRIC.SHIP s 
  ON DB01TD02.BRIC.SHIP.SHIP_NO = DB01TD02.BRIC.FLT_SKDL.SHIP_NO
WHERE
  s.SHIP_NO is null

I suspect your actual problem though, is that FLT_SKDL contains some rows with duplicate SHIP_NO

If the above query doesn't work, use the below query to identify duplicate ships and remove one of the duplicates:

SELECT * FROM DB01TD02.BRIC.FLT_SKDLWHERE ship_no IN( 
  SELECT ship_no FROM DB01TD02.BRIC.FLT_SKDL GROUP BY ship_no HAVING COUNT(*)
)
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • yes there is lots of duplicate SHIP_NO in FLT_SKDL table – niksan karkee Dec 13 '18 at 09:04
  • Well, you can't insert them into SHIP then, because SHIP has a primary key on SHIP_NO. Decide which rows you want to keep. Either by some rule that is intended to have only one ship considered, like `f.ShipIsActive = 1`, or by removing the duplicates from the `f` table, or by changing the query that does the insert so it looks more like `WHERE s.ship_no IS NULL AND f.SHIP_NO NOT IN (...list of ship no to exclude...)` or similar – Caius Jard Dec 13 '18 at 09:47
  • I tried with and *f.SHIP_NO NOT IN(select s.SHIP_NO from DB01TD02.BRIC.FLT_SKDL)* but the result is **(0 row processed)**. how can i list of ship no to exclude because *ship_no* can be anything within 10 character – niksan karkee Dec 14 '18 at 01:47
  • Like this: `NOT IN('abc123', 'def456' ...)` – Caius Jard Dec 14 '18 at 13:57