A few days ago, I ran into an interesting issue when creating a stored procedure to load new data into a 2014 Master Data Services entity via a staging table and a SQL Server stored procedure. As those familiar with the product know, incoming data (for any entity) requires a unique ‘code’ (a key) and the ‘name’/value for that particular key.

In this article I am going to show the reader how I achieved this.Codes are not confined to any one data type, thus in this case I decided to utilize consecutive integers.Obtaining the last and highest data ‘code’

The astute reader will note that when any record is ‘soft’ deleted from an entity, that the code of that record is set to a GUID. The record may be recovered, if and only if one knows which GUID belongs to that record. With the hard delete (which is what I utilized), the record is permanently deleted. This said and under normal circumstance the last record processed into the entity, should have the highest code value. This is what we are looking for.

declare @Kounter as intset @Kounter =(select max(convert(int,code)) from [staging].[AgingMDSInvoices])The Invoice Entity is similar to the table shown immediately below

Code

Name

Money Owed Attribute

6999

Frank Smith

$6789

6998

Paul Jones

$23000

6997

Sam Doe

$12.50

6991

Jane Eyre

$1666

6987

Frank N Stein

$2988

As mentioned immediately above, what we need to do is to find the maximum code that exists within the entity concerned. Thus the next potential key is obtained by adding 1 to that maximum code value.In our case (as seen above), the next code to be utilized is 7000.Assigning a Code to each new incoming recordHaving ascertained the highest code utilized thus far, the trick is to assign a new code to each record to be inserted into the MDS staging table.

To do this I decided to utilize a cursor.

Now I know that cursors are always a bad word in SQL Server, however the background is as follows.1) Each record read will be stored at first as a member of a table variable, thus not locking any tables.2) Daily transaction record amounts seldom exceed 5000 records.3) The system is standalone.

Now that the table variable has been created, the trick is to set up the necessary variables to be utilized within the cursor to process the incoming data (from the incoming SQL Server staging table) and to place this data into the table variable.

Now that we have the necessary infrastructure established, it is time to process the incoming data (from the staging table) and to assign the next consecutive code to the ‘next record’.Note that in this example, the staging table is a temporary table called #rawdata1 . It could just as well have been a disk based table.Upon opening the cursor, you will note that I have utilized the max(code) that I pulled at the top of this article (6999).To refresh your memory, this was the maximum existing code within the records of the Invoice entity.The code for the cursor and processing of this data is shown below:

DECLARE rt_cursor CURSORFORSELECT *FROM #rawdata1 --INCOMING DATA in STAGING TABLE--Note below that with each pass, @Kounter is incremented by 1

OPEN rt_cursorFETCH NEXT FROM rt_cursor INTO@ARDivisionNo,@CustomerNo,@name,@InvoiceNo,@InvoiceType,@InvoiceDate,@InvoiceDueDate,@SalespersonDivisionNo,@SalespersonNo,@CustomerPONo,@Balance,@UDF_INVC_CATEGORY,@UDF_WORKORDER,@UDF_BILL_TO_NAME,@UDF_SALES_ORDER_NO

FETCH NEXT FROM rt_cursor INTO@ARDivisionNo,@CustomerNo,@name,@InvoiceNo,@InvoiceType,@InvoiceDate,@InvoiceDueDate,@SalespersonDivisionNo,@SalespersonNo,@CustomerPONo,@Balance,@UDF_INVC_CATEGORY,@UDF_WORKORDER,@UDF_BILL_TO_NAME,@UDF_SALES_ORDER_NOEND

CLOSE rt_cursorDEALLOCATE rt_cursor

After having examined the cursor code above, it should become immediately apparent that it purpose is to ensure that each incoming record is assigned an unique and consecutive integer code value. Nothing more.All that is now left to do is to transfer the records within the cursor to an outbound staging table for final insertion into the Invoice Entity.Within the Master Data Services world, records to be processed require three additional fields appended to incoming records.

These fields are1) The import type (1= New record; 2= Update ; 4= Hard Delete) *2) The import status (set to zero by default). Upon successful loading this is changed by the system to 13) The batch tag. In my case and because the process is cyclical, I decided to keep the batch tag the same in all cases. In the code below the batch tag is set to 'Book Data3'

* For a complete listing of import type codes, the reader is referred to the following URL

http://technet.microsoft.com/en-us/library/ee633854.aspx

The code to place the incoming values, the import type, Import Status and Batch Tag into the final staging table is shown below:Insert into [staging].[OpenInvoicesMAS200NewRecords]select1 as ImportType,0 as [ImportStatus_ID],'Book Data3' as BatchTag,Kounter as code,[CustomerNo] as [name] ,[ARDivisionNo] ,[CustomerNo] ,[InvoiceNo] ,[InvoiceType] ,[InvoiceDate] ,[InvoiceDueDate] ,[SalespersonDivisionNo] ,[SalespersonNo] ,[CustomerPONo] ,[Balance] ,[UDF_INVC_CATEGORY] ,[UDF_WORKORDER] ,[UDF_BILL_TO_NAME] ,[UDF_SALES_ORDER_NO]from @NewAgingTablerecords

The staging process is now complete and ready to be loaded into Master Data Services. This is achieved utilizing the system stored procedures which are unique to each Master Data Services entity. All that remains to be done is to call the store procedure to load the entity.

Conclusion

Loading data into Master Data Services entities is neither difficult nor complex. Cursors, while not the best option, do have their own usefulness.

FETCH NEXT FROM rt_cursor INTO@ARDivisionNo,@CustomerNo,@name,@InvoiceNo,@InvoiceType,@InvoiceDate,@InvoiceDueDate,@SalespersonDivisionNo,@SalespersonNo,@CustomerPONo,@Balance,@UDF_INVC_CATEGORY,@UDF_WORKORDER,@UDF_BILL_TO_NAME,@UDF_SALES_ORDER_NOEND

Comments

Posted by Roddy.CAMERON on 2 January 2014

Hi Steve

You could dramatically simplify this into a single insert statement using a simple cte with your original 'select' and use the row_number() function to generate your incrementing counter as below. No need to create #rawdata1 or a table variable or use a cursor. Irrespective of performance, the code is now just a simple insert statement and much easier to understand for someone maintaining it. You go from a relatively long RBAR solution to a very simple, single statement, set based solution.