Solved

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

Posted on 2013-06-07
8
345 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
New and Previous Values in a Query 7 30
Text file into sql server 5 32
TSQL Challenge... 7 36
Where on a calculated field 1 22
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

828 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