question

asmagh-9402 avatar image
0 Votes"
asmagh-9402 asked Monalv-msft edited

IF EXISTS DATA IN A TABLE

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-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

asmagh-9402 avatar image
0 Votes"
asmagh-9402 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @asmagh-9402 ,

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.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @asmagh-9402 ,

May I know if you have anything to update?

Best regards,
Mona

0 Votes 0 ·