CODEWITHSUNDEEP
sql-server-2008insertduplicates
GLP
GLP

Reputation: 3675

How to insert conditionally

I create a temporary table #tbl(account, last_update). I have following two inserts from different source (could be tables from different databases) to insert account with last update date. For example

create table #tbl ([account] numeric(18, 0), [last_update] datetime)

insert into #tbl(account , last_update)
select table1.account, max(table1.last_update)
from table1 join…
group by table1.account

insert into #tbl(account , last_update)
select table2.account, max(table2.last_update)
from table2 join…
group by table2.account

The problem is this could cause duplicate account in the table #tbl. I either have to avoid it during each insert or remove the duplicate after both insert. Also, if there is account with two different last_update, I want the #tbl have the latest last_update. How do I achieve this conditional insert? Which one will have better performance?

Upvotes: 0

Views: 385

Answers (2)

AnuPrakash
AnuPrakash

Reputation: 51

 insert into #tbl(account , last_update)
  select  account, last_update
     from
      (
    select a.* from #table1 a where 
     last_update in( select top 1 last_update from #table1 b
      where 
        a.account = b.account
         order by last_update desc)
         UNION 
    select a.* from #table2 a where 
     last_update in( select top 1 last_update from #table2 b
      where 
        a.account = b.account
         order by last_update desc)
      ) AS tmp

Upvotes: 0

mbarthelemy
mbarthelemy

Reputation: 12913

Do you think you could rewrite your query to something like:

create table #tbl ([account] numeric(18, 0), [last_update] datetime)

insert into #tbl(account , last_update)
  select theaccount, MAX(theupdate) from 
  (
    select table1.account AS theaccount, table1.last_update AS theupdate
    from table1 join…
     UNION ALL
    select table2.account AS theaccount, table2.last_update AS theupdate
    from table2 join…
   ) AS tmp GROUP BY theaccount

The UNION ALL will build you 1 unique table combining table1 + table2 records. From there, you can act as if was a regular table, which means that you are able to find the max last_update for each record using a "group by"

Upvotes: 1

Related Questions