Filtering duplicate records while importing data from source table to target table

Question

This may be a simple query to some of you. But I am not strong in Sql, so expecting some solution for my problem.

I have 2 tables, ProductVenueImport and SupplierVenueImport.

We are dumping all the records from SupplierVenueImport to ProductVenueImport using MERGE clause and a Temp table. Temp will have valid records from SupplerVenuImport and from Temp table we are importing records to ProductVenueImport.

But before importing data to ProductVenueImport from Temp table I need to check for the duplicate records in my target (ProductVenueImport).

For example if I am importing a record with name as 'A', I need to look into ProductVenueImport whether 'A' already existing or not. If it is not existing then only I need to insert 'A' otherwise not.

Could somebody tell me how to do this?

Is using Cursors only the option?

Thanks, Naresh

Can u provide ur current query so that suggestions can be given accordingly. Also do u simply need to remove duplicate records from ur source table before inserting in target table? If yes i believe it could be done without cursor. Just try searching on net for the same and better try by yourself. — Nitin Agrawal
– Nitin Agrawal, Commented Jun 17, 2013 at 5:02
@NitinAgrawal, I need to remove the duplicates from 'Temp' table and at the same time I need to backup those records into another 'Temp' table for further usage. — Naresh
– Naresh, Commented Jun 17, 2013 at 5:04

Andriy M · Accepted Answer · 2013-06-17 05:39:51Z

1

Assuming the Temp table itself doesn't have duplicates, you could use MERGE like this:

Insert non-existing products.
Do a NO-OP in case of an existing product.
Use $action in the OUTPUT clause to mark which rows were considered for insertion (and inserted) and which for update (but not really updated).

This is what I mean:

DECLARE @noop int;  -- needed for the NO-OP below

MERGE INTO ProductVenueImport AS tgt
USING Temp AS src
ON src.ProductID = tgt.ProdutID
WHEN NOT MATCHED THEN
  INSERT (    column1,     column2, ...)
  VALUES (src.column1, src.column2, ...)
WHEN MATCHED THEN
  UPDATE SET @noop = @noop  -- the NO-OP instead of update
OUTPUT $action, src.column1, src.column2, ...
  INTO anotherTempTable
;

answered Jun 17, 2013 at 5:39

Andriy M

78.1k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2290627 · Accepted Answer · 2013-06-17 05:05:51Z

0

I think this would do this :

INSERT INTO PRODUCTTBL(FEILD1, FIELD2, FIELD3, FIELD4, FIELD5)
SELECT (FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) FROM TEMP WHERE CRITERIAFIELD NOT IN(SELECT     DISTINCT CRITERIAFIELD FROM PRODUCTTBL)

answered Jun 17, 2013 at 5:05

user2290627

Comments

Scott · Accepted Answer · 2013-06-17 05:14:37Z

0

This should allow you to check for duplicates in a table

select columnname from tablename group by columnname having count(columnname) >1

answered Jun 17, 2013 at 5:14

Scott

192 silver badges6 bronze badges

Comments

rahul · Accepted Answer · 2013-06-17 05:34:25Z

0

sorry if I am not getting the question right, can't you use the merge statement on the source table with "When not matched Insert" to insert the new records alone

so in your case it should be like this

merge into ProductVenueImport using temp on (<condition for duplicate>)
when not matched then insert <clause>;

the merge clause will make sure that no duplicate records are inserted into your source table.

answered Jun 17, 2013 at 5:34

rahul

5911 gold badge6 silver badges13 bronze badges

Collectives™ on Stack Overflow

Filtering duplicate records while importing data from source table to target table

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related