Introduction

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 int
set @Kounter  =(select max(convert(int,code)) from [staging].[AgingMDSInvoices])
The Invoice Entity is similar to the table shown immediately below

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

The code to create the table variable may be seen below:
DECLARE @NewAgingTablerecords TABLE (
Kounter int,
[ARDivisionNo] [varchar](2),
[CustomerNo] [varchar](20),
[name] [nvarchar](250)  ,
[InvoiceNo] [varchar](7),
[InvoiceType] [varchar](2)  ,
[InvoiceDate] [datetime]   ,
[InvoiceDueDate] [datetime]  ,
[SalespersonDivisionNo] [varchar](2)  ,
[SalespersonNo] [varchar](4)  ,
[CustomerPONo] [varchar](15)  ,
[Balance] [numeric](13, 2)  ,
[UDF_INVC_CATEGORY] [varchar](13)  ,
[UDF_WORKORDER] [varchar](15)  ,
[UDF_BILL_TO_NAME] [varchar](30) ,
[UDF_SALES_ORDER_NO] [varchar](7)  )

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.

declare
@ARDivisionNo  [varchar](2),
@CustomerNo [varchar](20),
@name  [nvarchar](250)  ,
@InvoiceNo  [varchar](7),
@InvoiceType  [varchar](2)  ,
@InvoiceDate  [datetime]   ,
@InvoiceDueDate  [datetime]  ,
@SalespersonDivisionNo  [varchar](2)  ,
@SalespersonNo  [varchar](4)  ,
@CustomerPONo  [varchar](15)  ,
@Balance  [numeric](13, 2)  ,
@UDF_INVC_CATEGORY [varchar](13)  ,
@UDF_WORKORDER  [varchar](15)  ,
@UDF_BILL_TO_NAME  [varchar](30) ,
@UDF_SALES_ORDER_NO  [varchar](7)

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 CURSOR
FOR
SELECT *
FROM #rawdata1   --INCOMING DATA in STAGING TABLE
--Note below that with each pass, @Kounter is incremented by 1

OPEN rt_cursor
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_NO

WHILE @@FETCH_STATUS = 0

BEGIN
SET @Kounter = @Kounter +1
INSERT @NewAgingTablerecords VALUES ( @Kounter,
@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_NO
END

CLOSE rt_cursor
DEALLOCATE 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 are
1)      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 1
3)      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]
select
1 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.

As always, should you have any questions or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy programming.

The complete code sample

/****** Script for SelectTopNRows command from SSMS  ******/

use AgingReport
go

--IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
--BEGIN
--     DROP TABLE #rawdata1
--END

--IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
--BEGIN
--     DROP TABLE #rawdata2
--END

--go

Create Procedure MDSFormatNEWincomingRecordsMAS200
as
declare @Kounter as int
set @Kounter  =(select max(convert(int,code)) from [staging].[AgingMDSInvoices])
SELECT
[ARDivisionNo]
,mas200.CustomerNo, aging.name
,mas200.InvoiceNo
,mas200.InvoiceType
,mas200.InvoiceDate
,mas200.InvoiceDueDate
,mas200.SalespersonDivisionNo
,mas200.SalespersonNo
,mas200.CustomerPONo
,mas200.Balance
,mas200.UDF_INVC_CATEGORY
,mas200.UDF_WORKORDER
,mas200.UDF_BILL_TO_NAME
,mas200.UDF_SALES_ORDER_NO
into #rawdata1
From [staging].[OpenInvoicesMAS200] mas200
left outer join [staging].[AgingMDSInvoicesWithRemoveFlagSet] aging
on aging.name = mas200.customerno
and
(case when isnumeric(aging.InvoiceNo)=1  then '00'+ aging.InvoiceNo else aging.InvoiceNo end ) = mas200.invoiceNo
and aging.InvoiceType = mas200.Invoicetype
where aging.balance is null

DECLARE @NewAgingTablerecords TABLE (
Kounter int,
[ARDivisionNo] [varchar](2),
[CustomerNo] [varchar](20),
[name] [nvarchar](250)  ,
[InvoiceNo] [varchar](7),
[InvoiceType] [varchar](2)  ,
[InvoiceDate] [datetime]   ,
[InvoiceDueDate] [datetime]  ,
[SalespersonDivisionNo] [varchar](2)  ,
[SalespersonNo] [varchar](4)  ,
[CustomerPONo] [varchar](15)  ,
[Balance] [numeric](13, 2)  ,
[UDF_INVC_CATEGORY] [varchar](13)  ,
[UDF_WORKORDER] [varchar](15)  ,
[UDF_BILL_TO_NAME] [varchar](30) ,
[UDF_SALES_ORDER_NO] [varchar](7)  )

declare
@ARDivisionNo  [varchar](2),
@CustomerNo [varchar](20),
@name  [nvarchar](250)  ,
@InvoiceNo  [varchar](7),
@InvoiceType  [varchar](2)  ,
@InvoiceDate  [datetime]   ,
@InvoiceDueDate  [datetime]  ,
@SalespersonDivisionNo  [varchar](2)  ,
@SalespersonNo  [varchar](4)  ,
@CustomerPONo  [varchar](15)  ,
@Balance  [numeric](13, 2)  ,
@UDF_INVC_CATEGORY [varchar](13)  ,
@UDF_WORKORDER  [varchar](15)  ,
@UDF_BILL_TO_NAME  [varchar](30) ,
@UDF_SALES_ORDER_NO  [varchar](7)

DECLARE rt_cursor CURSOR
FOR
SELECT *
FROM #rawdata1
--ORDER BY YearMth

OPEN rt_cursor
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_NO
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Kounter = @Kounter +1
INSERT @NewAgingTablerecords VALUES ( @Kounter,
@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_NO
END

CLOSE rt_cursor
DEALLOCATE rt_cursor
Insert into [staging].[OpenInvoicesMAS200NewRecords]
select
1 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