CODEWITHSUNDEEP
sqlsql-servert-sqlinsertsql-insert
jwalls91
jwalls91

Reputation: 351

Insert into table only distinct rows?

I have temp table #A which contains:

ID        RevID     Designation      ParentID     Amount
--------------------------------------------------------    
100       500       Test1            300          5000
250       500       Test2            360          5000
410       500       Test3            580          5000

I also have a temp table #B which contains:

ID        RevID     Text        Amount
--------------------------------------  
900       500       Test100      6500

What I am trying to do is insert what's in table #B without it duplicating rows due to the left join based on the RevID. I only care about bringing in the RevID and the Amount from table #B to a new row in table #A, and keeping the other columns the same as any row with a matching RevID from table #A.

I am trying to insert into this table by doing:

INSERT INTO #A (ID, RevID, Designation, ParentID, Amount)
    SELECT DISTINCT
        a.ID,
        a.RevID,
        b.Text,
        a.ParentID,
        b.Amount
    FROM 
        #B b
    LEFT JOIN
        #A a ON b.RevID = a.RevID

My ideal results is the following where it adds that one line from table #B to table #A without it adding it three times due to the three rows from table #A with the same RevID:

ID        RevID     Designation      ParentID     Amount

100       500       Test1            300          5000
250       500       Test2            360          5000
410       500       Test3            580          5000
100       500       Test100          300          6500

I don't think I can use union because table #A may contain other RevID and also table #B does not contain the same columns as table #A so I will need to join with table #B and where there is a matching RevID, only bring in that row from table #B to table #A once.

Upvotes: 0

Views: 142

Answers (3)

Abhijeet Khandagale
Abhijeet Khandagale

Reputation: 89

Considering that you are only concerned about adding distinct RevID and Amount columns from #B to #A where remaining columns are not important, you may try following query and check if it satisfies all your conditions,

INSERT INTO #A (ID, RevID, Designation, ParentID, Amount)
SELECT (SELECT TOP 1 #A.ID FROM #A) ID, a.RevID,(SELECT TOP 1 #A.Designation FROM #A) Designation,(SELECT TOP 1 #A.ParentID FROM #A) ParentID,b.Amount FROM  #B b JOIN #A a ON b.RevID = a.RevID
EXCEPT
SELECT (SELECT TOP 1 #A.ID FROM #A) ID,RevID,(SELECT TOP 1 #A.Designation FROM #A) Designation,(SELECT TOP 1 #A.ParentID FROM #A) ParentID,Amount FROM #A

Using EXCEPT will avoid rows which are already there in #A and we will get unique rows for insertion query.

Please refer DB Fiddle with more sample data: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=de51af7d0e1e7ff0407c445a301c5560

Upvotes: 1

Stu
Stu

Reputation: 32579

This can be easily done using a cross apply to select a single matching row:

insert into #a (Id, RevId, Designation, ParentId, Amount)
select a.Id, a.RevId, b.[Text], a.ParentId, b.Amount
from #b b
cross apply (
    select top(1) a.Id, a.RevId, a.ParentId
    from #a a
    where a.RevId=b.RevId
    order by a.Id
)a

Upvotes: 1

EdmCoff
EdmCoff

Reputation: 3576

You could do something like:

INSERT INTO #A (ID, RevID, Designation, ParentID, Amount)
 SELECT
  (SELECT TOP 1 id FROM #A a WHERE a.RevId = b.RevId),
  b.RevID,
  b.Text,
  (SELECT TOP 1 ParentId FROM #A a WHERE a.RevId = b.RevId)
  b.Amount
 FROM 
  #B b

That's not pretty, but I think it follows from your description very logically. Get the rows from B and keep "the other columns the same as any row with a matching RevID from table #A"

Alternatively, I might suggest:

INSERT INTO #A (ID, RevID, Designation, ParentID, Amount)
 SELECT
  a.ID,
  a.RevID,
  b.Text,
  a.ParentID,
  b.Amount
 FROM 
  #B b
  LEFT JOIN
  (
   SELECT a.RevId, MAX(a.ID) ID, MAX(a.ParentID) ParentID
   FROM #A a
   GROUP BY RevId
  ) a ON b.RevID = a.RevID

The inner query gets one row per RevId from #A with an arbitrary ID and ParentID. That can be joined with #B without creating duplicates.

Upvotes: 1

Related Questions