CODEWITHSUNDEEP
sql-server-2008ssis
Full_Int
Full_Int

Reputation: 429

Removing duplicate entries which would exist after insert

I have a small problem with inserting information from one table to other.

For example: from table A (can contain ~10 kk entries) to table B, tables are identical except table A has DateTimeStamp which is used to take certain data (boundaries).

So I need to move data from A to B (without DateTimeStamp) and remove duplicates from B.

Example:

Table A

DateTimeStamp | Key | value 
2012-02-03    |  2  | 123
2012-02-03    |  3  | 985
2012-02-03    |  5  | 1584

Table B

Key  | value 
  8  | 45
  3  | 785
  9  | 7457

So I need to delete row with Key = 3 from Table B and insert everything else from Table A.

Results would be:

Key  | value 
  8  | 45
  3  | 985
  9  | 7457
  2  | 123
  5  | 1584

Is there elegant way to do this ? Triggers are too slow, and I am looking for solution that wouldn't require temporary table.

SQL Server or SSIS solutions\suggestions are welcome

Upvotes: 3

Views: 536

Answers (4)

Diego
Diego

Reputation: 36126

even though all solutions here presented seem to work, I would really build a package yo deal with a table with 10kk rows.

you can use a oledbsource with the query aF. suggested:

select a.Key, a.Value 
from tableA a 
where a.Key not in (select b.Key from tableB b)

and send it directly to a oledbdestination pointing to table B

Upvotes: 0

Kapil Khandelwal
Kapil Khandelwal

Reputation: 16134

Try this:

Remove Duplicates from table B:

    delete FROM tableB Where tableB.Key IN
    (select Key
    from tableA 
    INTERSECT
    select Key
    from tableB)

Then, Insert into table B:

 insert INTO tableB (Key, Value)
select Key, Value From tableA

Upvotes: 0

marc_s
marc_s

Reputation: 754230

If you're using SQL Server 2008 or newer, you could do this very easily with a single MERGE statement - something like this:

MERGE INTO dbo.B                -- target table
USING A ON b.Key = a.Key        -- source table and "link" information
WHEN MATCHED 
    THEN UPDATE SET B.Value = A.Value   -- if "Key" already present in B - update "Value"
WHEN NOT MATCHED                        -- if "Key" not present in B - insert new row
    THEN INSERT(TblKey, TblValue) VALUES(A.TblKey, A.TblValue) 
WHEN NOT MATCHED BY SOURCE
    DELETE                              -- if present in B, but not in A -> remove
;

Upvotes: 3

aF.
aF.

Reputation: 66687

Two steps:

-- first delete
delete tableB
from tableB b
inner join tableA a on b.Key = a.Key

-- then insert
insert into tableB
select a.Key, a.Value from tableA a where a.Key not in (select b.Key from tableB b)

Upvotes: 1

Related Questions