Solved

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

Posted on 2013-06-07
8
341 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
 

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
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.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
powershell and sql server - alerting 7 77
Update in Sql 7 30
Need help debbuging stored procedure 21 32
SQL Error in WHERE Clause 5 37
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now