IF EXISTS DATA IN A TABLE

asma gh 21 Reputation points
2021-03-17T10:16:02.78+00:00

hello , please I would like to know how to apply a control rule (exists) on my data indeed I load data from several excel files (file of each week) in a table and sometimes the same row with the same information is in 2 files and I would like to embed a script that will check if this line already exists so it doesn't add it but if it's exists it will not insert the duplicated row , an idea please thank youu

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,452 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vaibhav Chaudhari 38,576 Reputation points
    2021-03-17T11:31:03.887+00:00

    Implementation depends on your scenario

    Option 1 - Truncate the data first and load data from Excel every time

    Option 2 - Load data from Excel to Staging table and run SP to get only unique records loaded to final table

    Option 3 - After excel load, run SP to remove duplicate, unwanted data from your final table

    Reference:
    https://www.mssqltips.com/sqlservertip/1918/different-strategies-for-removing-duplicate-records-in-sql-server/
    https://www.mssqltips.com/sqlservertip/4486/find-and-remove-duplicate-rows-from-a-sql-server-table/

    Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav

    0 comments
  2. asma gh 21 Reputation points
    2021-03-17T13:29:05.387+00:00

    i use a row_number over ( partition by ) and select only how has row_number = 1

    0 comments
  3. Monalv-MSFT 5,891 Reputation points
    2021-03-18T01:50:30.91+00:00

    Hi @asma gh ,

    May I know if you use ssis package in SSDT?

    If so, we can use Lookup Transformation between Excel Source and OLEDB Destination , and then load NoMatch data into OLEDB Destination.

    Please refer to An Overview of the LOOKUP TRANSFORMATION in SSIS.

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.