Distinct Insert? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Distinct Insert?

Author

Topic

chrpeter
Starting Member

31 Posts

Posted - 2008-10-15 : 17:05:53

I have an invoice detail table that I need to pull two fields from and put into another table.

Is there a way to pull distinct fields from that table so that there are no duplicates inserted into the destination table?

Here's an example of the data

InvDet Table (Invoice Detail Table)
INV_NO - PART_NO - GL_NO - CO_NO
1 ______ 123 _____ 900 ____ 001
1 ______ 111 _____ 900 ____ 001
2 ______ 111 _____ 901 ____ 001
2 ______ 123 _____ 901 ____ 001
2 ______ 999 _____ 902 ____ 001

Here's what I need to end up with
LogInv Destination table
INV_NO - CO_NO
1 ______ 001
2 ______ 001

The catch is the destination table is being populated via a trigger on the detail table. The GL_NO is originally set to 0, when it's given a value(not 0), then I need the trigger to pull the INV_NO and CO_NO into the destination table. But only one line per INV_NO.

Here's what I have so far, right now it will insert duplicates.

INSERT INTO LogInv
       (inv_no, co_no, action, hdr_processed_flag, dtl_processed_flag )
	SELECT 	
		 inv_no, co_no , 'I', 0, 0
	FROM inserted 
	WHERE GL_NO > 0 AND inv_no in (Select inv_no from DELETED where GL_NO = 0)

Keep in mind this statement is executed inside an UPDATE trigger.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-15 : 18:15:17

and why can't you use distinct??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 03:07:26

you could simply use distinct as sugested. however if your scenario is different please post some sample to illustrate it so that we can understand why distinct is not working.

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-16 : 03:50:12

What if Co_No is different for the duplicate Inv_No? Which Co_No will you use?

Why are you not considering Group By and max(Co-No)?

SELECT inv_no, max(co_no) , 'I', 0, 0
FROM inserted
WHERE GL_NO > 0 AND inv_no in (Select inv_no from DELETED where GL_NO = 0)
GROUP BY inv_no

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources