checking of duplicate entry with BULK INSERT !!!!!

• Navneet-390901

  SSC-Addicted

  Points: 425

  More actions

  February 1, 2007 at 9:57 am

  #194131

  Could anyone tell me how to check the duplicate entry or even add only the distinct entry into the database through the bulk insert. I want to do the bulk insert , it first should check the duplicate data in the file and then only one data of duplicate (distinct) data should be added into the database through the bulk insert.

  I have one file which consists of only one coloumn (phone_no) entry and i want to bulk insert into the database and for this also, if there is already the number is there in the database then it should not be added into the database. So, THERE IS ONLY TWO CONDITION IS TO CHECK, FIRST CHECK THE DISTINCT PHONE NUMBER DURING THE BULK INSERT AND SECOND CHECK SHOULD BE EXISTING TABLE , IF IT CONSISTS OF SAME PHONE NUMBER WHICH IS THERE IN THE FILE THEN SHOULD NOT BE ADDED INTO THE DATABASE.

  so please help me out in this regard. or even give any kind of hint for this....

  in advance thanks ....

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  February 1, 2007 at 10:13 am

  #687157

  2 solutions:

  1. Create a staging table. On every load, truncate the staging table, then perform the BULK INSERT to the staging table and not your permanent table.

  Once data is in the staging table, perform an INSERT with a WHERE NOT EXISTS:

  INSERT INTO MainTable

    (Phone_No)

  SELECT DISTINCT Phone_No

  FROM StagingTable AS s

  WHERE NOT EXISTS (

    SELECT *

    FROM MainTable As t

    WHERE t.Phone_No = s.Phone_No

  )

  2. Bring the data in using SSIS instead of BULK INSERT. Configure the SSIS data flow to only insert records that aren't already in the table.