Solved

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

Posted on 2013-06-07
8
342 Views
Last Modified: 2013-06-11
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
0
Comment
Question by:DennisStickles
8 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 39229757
You can try INSERT/UPDATE triggers and insert/update data to history table in the trigger from INSERTED/UPDATED tables.
0
 
LVL 18

Assisted Solution

by:UnifiedIS
UnifiedIS earned 167 total points
ID: 39229961
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 333 total points
ID: 39233607
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:DennisStickles
ID: 39234951
@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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 333 total points
ID: 39236627
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
 

Author Comment

by:DennisStickles
ID: 39238175
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39238639
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
 

Author Comment

by:DennisStickles
ID: 39238662
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question