Solved

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

Posted on 2013-06-07
8
340 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 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

13 Experts available now in Live!

Get 1:1 Help Now