Before I start going into code, please allow me to explain a few details about my system. I have four company databases that I want to merge into one. I also want to take some tables and merge them into a single table. In addition, I want to do all of this without affecting the performance of Dynamics GP. The way that I came up with was to first, import data from a specific table in each company to a single table in my "Stage 1" database. Then create "knowledge base" tables using those imported tables in my either in the "Stage 1" database itself or somewhere between the "Stage 1" database and the final BI Warehouse.

Therefore, here we go, from the code in one of my previous post in this series you noticed a stored procedure named id_upr00100. As I promised in that post here is the code for that procedure. In addition, as you look over this code please keep this post in mind.

Create Procedure id_upr00100 with encryption as

Begin
    SET NOCOUNT ON

    truncate table DYN.dbo.upr00100

    insert into DYN.dbo.upr00100 
          select 'Jackson  ' as Corp, * from gpjac.dbo.upr00100 as upr00100 with (nolock)
               union
          select 'Carthage ' as Corp, * from gpcar.dbo.upr00100 as upr00100 with (nolock)
               union
          select 'Greenwood' as Corp, * from gpgre.dbo.upr00100 as upr00100 with (nolock)
               union
          select 'Hospice  ' as Corp, * from hmain.dbo.upr00100 as upr00100 with (nolock)
 end

Notice that it is inserting data into at table name upr00100 yet is has an extra field at the beginning.

The upr00100 table in my "Stage 1" database was created by using SSMS to script out the table structure of the table in one of my company databases as a create script. However, before I run the script in my "Stage 1" database I have to change a few things.

  • a) Add a field for the company name
  • b) Remove all indexes, constraints, auto increment properties, etc

I do that for these reasons, the company field is so I know which database the row of data came from. The auto identity property from the DEX_ROW_ID field is removed because with multiple company databases, it is possible to have the same number in different databases, and it makes the inserts easier and faster. Moreover, all the indexes and constraints go back to one of my design goals. I do not want to harm GP performance. By removing all constraints, indexes, etc, I have just a heap. Remember, I want to get the data from Dynamics GP to this table as fast as possible, by having a heap I can get the data from Dynamics GP into this table without having any indexes to update in the destination table as the data is pulled.

In this specific procedure, considering I have less than 1000 employees across four companies, this one is simple but doing this on a bigger table can cause serious performance problems. However, I will go into the different ways of getting data into a "Stage 1" database table on a different post series, as there are multiple ways of doing that this depending on table size. I have a link at the bottom of this post to direct you there the post series is called Getting Data from Point A to Point B. Just make sure you read that series as I actually have a better way of doing this exact script using for insert statements versus the single insert statement with unions. However, I will save that for the other series.

<Previous Post in this Series> <Next Post in this Series>

New Series: Getting Data from Point A to Point B

Till later,
John