Using merging but still getting dups

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 2012 Forums

Transact-SQL (2012)

Using merging but still getting dups

Author

Topic

PoseyRobert
Starting Member

27 Posts

Posted - 2014-12-26 : 18:09:49

Here my issue.

I want to be able to only to only total on 1 row and zero you the other row.

Example
<b>
Location Tag ID Original Count New Count
W013 51453 26 2
W013 51454 26 24
</b>

My total count is only 26. I want to be able to only show 26 on 1 row and zero on the other.

The was my original count is not showing a duplicate count.

I am creating my temp file first then I am trying to add only 1 tag_id to the create temp record using a merge.

I am trying to use the below merge and having not luck.

I do not want to do an insert in the merge.

-- Create temp records first
INSERT INTO #InventoryCountTemp (Item_Code, Facility_Code, Count_Location_Code, Count_Lot_Number_ID, Tag_ID)
select Item_Code, Facility_Code, Count_Location_Code, Lot_Number_ID, NULL
from PHYSICAL_INVENTORIES_TAGS

-- Merge in the missing items
MERGE INTO #InventoryCountTemp AS target
USING PHYSICAL_INVENTORIES_TAGS AS source
ON target.Item_Code = source.Item_Code
AND target.Facility_Code = source.Facility_Code
AND target.Count_Location_Code = source.Count_Location_Code
AND target.Count_Lot_Number_ID = source.Lot_Number_ID
WHEN MATCHED and target.Tag_ID is null
THEN UPDATE SET target.Tag_ID = source.Tag_ID
WHEN NOT MATCHED BY TARGET and source.Item_Code is not null THEN
INSERT (Item_Code, Facility_Code, Count_Location_Code, Count_Lot_Number_ID, Tag_ID)
VALUES (source.Item_Code, source.Facility_Code, source.Count_Location_Code, source.Lot_Number_ID, source.Tag_ID)
;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources