How should I OUTPUT from this MERGE into a transaction log table

First a little background.

I have  three tables:

Product - This table contains the product definition
ProductInventory - This table contains the product inventory
ProductInventoryTransactionHistory - This table contains the history of all update or inserts performed on the ProductInventory table.

Here are the table details:

CREATE TABLE [PartType].[Product](
	[PK_ProductID] [int] IDENTITY(1,1) NOT NULL,
	[partno] [varchar](15) NOT NULL,
	[parttype] [varchar](20) NULL,
	[descript] [varchar](35) NULL,
	[catindex] [varchar](3) NULL,
	[manufacter] [varchar](25) NULL,
	[modelno] [varchar](23) NULL,
	[pounit] [varchar](6) NULL,
	[poratio] [numeric](13, 6) NULL,
	[unit] [varchar](6) NULL,
	[FK_UnitMeasureCode] [nchar](3) NULL,
	[altpartno] [varchar](max) NULL,
	[lastvename] [varchar](41) NULL,
	[lastvendid] [varchar](9) NULL,
	[obsolete] [bit] NULL,
	[obsdate] [date] NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[PK_ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_ProductPartno] UNIQUE NONCLUSTERED 
(
	[PK_ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window

CREATE TABLE [PartType].[ProductInventory](
	[PK_ProductInventoryID] [int] IDENTITY(1,1) NOT NULL,
	[FK_ProductID] [int] NOT NULL,
	[FK_LocationID] [smallint] NOT NULL,
	[partno] [varchar](15) NOT NULL,
	[LocationCode] [nvarchar](15) NULL,
	[QuantityReceived] [numeric](12, 3) NULL,
	[QuantityRemaining] [numeric](12, 3) NULL,
	[LotNumber] [varchar](50) NULL,
	[ReceiverNumber] [varchar](6) NULL,
	[ReceiverItemNumber] [varchar](4) NULL,
	[ReceivedDate] [date] NULL,
	[ExpirationDate] [date] NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductInventory] PRIMARY KEY CLUSTERED 
(
	[PK_ProductInventoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

CREATE TABLE [PartType].[ProductInventoryTransactionHistory](
	[TransactionID] [int] IDENTITY(1,1) NOT NULL,
	[TransactionTimeStamp] [datetime] NULL,
	[TransactionType] [varchar](50) NULL,
	[ProductID] [int] NULL,
	[partno] [varchar](15) NULL,
	[LotNumber] [varchar](50) NULL,
	[parttype] [varchar](20) NULL,
	[descript] [varchar](35) NULL,
	[catindex] [varchar](3) NULL,
	[manufacter] [varchar](25) NULL,
	[modelno] [varchar](23) NULL,
	[pounit] [varchar](6) NULL,
	[poratio] [numeric](13, 6) NULL,
	[Quantity] [int] NULL,
	[unit] [varchar](6) NULL,
	[FK_UnitMeasureCode] [nchar](3) NULL,
	[altpartno] [varchar](max) NULL,
	[lastvename] [varchar](41) NULL,
	[lastvendid] [varchar](9) NULL,
	[obsolete] [bit] NULL,
	[obsdate] [date] NULL,
	[rowguid] [uniqueidentifier] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductInventoryTransactionHistory] PRIMARY KEY CLUSTERED 
(
	[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window



I use this MERGE below to keep the ProductInventory table up to date using data from a 2nd system.  

MERGE Production.PartType.ProductInventory AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON ptpi.partno = MRPsld.partno
AND ptpi.ReceivedDate = MRPsld.sndate
AND ptpi.ReceiverNumber = MRPsld.docno
AND ptpi.ReceiverItemNumber = MRPsld.docitem
AND ptpi.LotNumber = MRPsld.snlotno
AND ptpi.ExpirationDate = MRPsld.expirdate	
WHEN MATCHED AND ptpi.LocationCode != MRPsld.vendlotno
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno
WHEN NOT MATCHED THEN
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate);

Open in new window


I'd like to OUTPUT the updates/inserts to the ProductInventory table to the ProductInventoryTransactionHistory table.

The catch is I need to join the updated / inserted information from ProductInventory  table with the appropriate product definition from the Product table and place all of the information into the TransactionLog table. I'd be joining on partno from each table.

My first thought was to use SCOPE_IDENTITY() to get the PK_ProductInventoryID that was updated or inserted.  Then Select the columns I want from the ProductInventory  table using the PK_ProductInventoryID , JOIN as stated above, then insert into the ProductInventoryTransactionHistory table.

I'm new to this so I wanted to get your guys input before I go off and try to get this working.

Thanks!

-Dennis
DennisSticklesAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
I am confused how using SCOPE_IDENTITY() is going to help when you could have added hundreds of rows.  Further, why even bother when OUTPUT will give you the result of adding the IDENTITY value it to the table.  What am I missing?
0
 
SharathData EngineerCommented:
You can try INSERT/UPDATE triggers and insert/update data to history table in the trigger from INSERTED/UPDATED tables.
0
 
UnifiedISConnect With a Mentor Commented:
I'm not a fan of triggers so using SCOPE_IDENTITY() would be my preference.

You need to wrap your insert and scope_identity calls with a begin end to ensure that you get the correct identity value.

BEGIN
INSERT...
SELECT @variable = SCOPE_IDENTITY()
UPDATE...WHERE ... = @variable

END
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DennisSticklesAuthor Commented:
@acperkins

I doubt you're missing anything. I'm new to this so I'm the that didn't understand how SCOPE_IDENTITY() worked with a MERGE.

What I'm trying now is using OUTPUT to fill a temporary table with the ID of the row that changed and the $action. I'm then trying to use an INSERT INTO to update the ProductInventoryTransactionHistory table. I've just about go it working and will post the final code when I do.

Thanks for your input.

-Dennis
0
 
Anthony PerkinsConnect With a Mentor Commented:
What I'm trying now is using OUTPUT to fill a temporary table with the ID of the row that changed and the $action.
Absolutely.  That is the right direction.  That will give you all the IDENTITY values added.
0
 
DennisSticklesAuthor Commented:
Below is the final code that works. I've added the transaction statements in preparation for some error handling.

I'll may throw out another question about the proper way to do the error handling, but for now I need to move on to the next part of the project.

USE Production;
GO
-- =============================================
-- Author:		Dennis 
-- Create date: 6/10/2013
-- Description:	
-- Using the MERGE statement this proceedure updates Production.PartType.ProductInventory. 
-- First it checks for location updates, making sure that all of the other important columns
-- match, then it checks the location. If everything but the location match, the location is 
-- updated. 
-- The WHEN NOT MATCHED of the MERGE statement adds row if any of the important columns DON'T
-- match. In the case the location is not looked at.
--
-- During the inserts the @ids variable table is updated with the PK_ProductInventoryID from the
-- Production.PartType.ProductInventory table and the $action the MERGE performed. FYI, 'inserted'
-- covers updated and inserted rows. 
-- 
-- Once the Production.PartType.ProductInventory table has been updated, the INSERT INTO records
-- the transactions into the ProductInventoryTransactionHistory table along with the transaction
-- action/type (Update or Inserted). 
--
-- NOTE: CURRENTLY NO ENTRYS ARE DELETED FROM THE ProductInventory TABLE. THIS FEATURE MAY BE ADDED
-- IN THE FUTURE.
-- =============================================


--Declare temp table to store new / updated information
DECLARE @ids as table (TransactionType varchar(15), id int);

-- The 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' statment with SERIALIZABLE does the following:
-- Specifies the following: •Statements cannot read data that has been modified but not yet committed by other transactions.
--							•No other transactions can modify data that has been read by the current transaction until the current transaction completes.
--							•Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	
BEGIN TRANSACTION UpdateInventoryTable;

MERGE Production.PartType.ProductInventory WITH ( UPDLOCK ) AS ptpi
USING (SELECT partno,sncv, sndate, doctype, docno, detqty, snlotno, docitem,vendlotno, expirdate
FROM pcMRP.dbo.SNLOTDET
WHERE doctype = 'R' and sndate > '2013-01-01') AS MRPsld
ON ptpi.partno = MRPsld.partno
AND ptpi.ReceivedDate = MRPsld.sndate
AND ptpi.ReceiverNumber = MRPsld.docno
AND ptpi.ReceiverItemNumber = MRPsld.docitem
AND ptpi.LotNumber = MRPsld.snlotno
AND ptpi.ExpirationDate = MRPsld.expirdate	
WHEN MATCHED AND ptpi.LocationCode != MRPsld.vendlotno
THEN UPDATE SET ptpi.LocationCode = MRPsld.vendlotno
WHEN NOT MATCHED THEN 
	INSERT (FK_ProductID,FK_LocationID, partno, LocationCode, QuantityReceived, QuantityRemaining, LotNumber, ReceiverNumber, ReceiverItemNumber, ReceivedDate, ExpirationDate)
	VALUES ((SELECT PK_ProductID FROM Production.PartType.Product WHERE partno = MRPsld.partno),10, partno, vendlotno, detqty, detqty, snlotno, docno, docitem, sndate, expirdate)
OUTPUT $action, inserted.PK_ProductInventoryID into @ids;

COMMIT TRANSACTION UpdateInventoryTable;

BEGIN TRANSACTION UpdateTransHistoryTable;

INSERT INTO [PartType].[ProductInventoryTransactionHistory]
SELECT  (getdate()), 
		idvar.TransactionType
		, idvar.id
		, jptpi.[partno]
		, jptpi.[LotNumber]
		, jptpi.[LocationCode]
		, jptpi.[QuantityReceived]
		, jptpi.[QuantityRemaining]
		, jptpi.[ReceiverNumber]
		, jptpi.[ReceiverItemNumber]
		, jptpi.[ReceivedDate]
		, jptpi.[ExpirationDate]
		, jptp.[parttype]
		, jptp.[descript]
		, jptp.[catindex]
		, jptp.[manufacter]
		, jptp.[modelno]
		, jptp.[pounit]
		, jptp.[poratio]
		, jptp.[unit]
		, jptp.[FK_UnitMeasureCode]
		, jptp.[altpartno]
		, jptp.[lastvename]
		, jptp.[lastvendid]
		, jptp.[obsolete]
		, jptp.[obsdate]
		, jptpi.rowguid
		, (getdate()) 
		FROM @IDs idvar 
INNER JOIN [PartType].[ProductInventory] jptpi
ON idvar.id = jptpi.PK_ProductInventoryID
INNER JOIN [PartType].[Product] jptp
ON jptpi.FK_ProductID = jptp.PK_ProductID;

COMMIT TRANSACTION UpdateTransHistoryTable;

Open in new window


 -Dennis
0
 
Anthony PerkinsCommented:
There is not point in doing BEGIN / COMMIT TRANSACTION for each SQL statement. The operation will either succeed or fail in its entirety.  The only time Transactions are called for is when there are two or more SQL statement and the subsequent statement depends on the previous one.  If that is the case here than it should be:

BEGIN TRANSACTION
MERGE ...
INSERT ...
COMMIT TRANSACTION

All wrapped in a TRY CATCH.
0
 
DennisSticklesAuthor Commented:
That makes more sense then what I was thinking because, in this case where the insert is dependent on the MERGE, I'd want to rollback the MERGE if the INSERT failed.

That way I could attempt the entire operation again. Which is the only way I could update my ProductInventoryTransactionHistory table since the OUTPUT of what changed in the ProductInventory table is stored in a var table and lost when the process is complete. (just a little thinking out loud)

I'll make the change and post the results.

Thanks!

-Dennis
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.