SQL Server function to get Business Unit Guid by Name

This will be very useful when you are migrating data to CRM database from some other system database. Specially when filling base tables we should give business unit guid to it.
You can use following function and I have given the way that you should use it in your data migration scripts.
CREATE FUNCTION dmfx_GetBusinessUnit
(   
    @businessUnitName nvarchar(30)
)
RETURNS TABLE
AS
RETURN
    SELECT [BusinessUnitId] AS BusinessUnit
    FROM [TEST_MSCRM].[dbo].[BusinessUnitBase]
    WHERE Name=@businessUnitName
GO
Use\Invoke the above function as follows…

INSERT INTO [TEST_MSCRM].[dbo].[TEST_regionBase]
      (
      [CPMS_regionId]
      ,[CreatedOn]
      ,[CreatedBy]
      ,[ModifiedOn]
      ,[ModifiedBy]
      ,[OwningUser]
      ,[OwningBusinessUnit]
      ,[statecode]
      ,[statuscode]
      ,[DeletionStateCode]
      ,[ImportSequenceNumber]
      ,[OverriddenCreatedOn]
      ,[TimeZoneRuleVersionNumber]
      ,[UTCConversionTimeZoneCode] 
      )
SELECT
     [GUID] as TEST_regionId]
      ,GETDATE() AS [Created]
      ,(SELECT DataMigrationUser FROM dmfx_GetDataMigrationUserId()) AS [CreatedBy]    —    Data Migration User
      ,GETDATE() AS [Modified]                           
      ,(SELECT DataMigrationUser FROM dmfx_GetDataMigrationUserId()) AS [ModifiedBy]    —    Data Migration User
      ,(SELECT DataMigrationUser FROM dmfx_GetDataMigrationUserId()) AS [OwningUser]    —    Data Migration User
      ,(SELECT BusinessUnit AS [OwningBusinessUnit] FROM dmfx_GetBusinessUnit(‘TESTBUSINESSUNIT’)) AS BusinessUnit     —   
TESTBUSINESSUNIT
      ,0 AS [statecode]
      ,1 AS [statuscode]
      ,0 AS [DeletionStateCode]
      ,null AS [ImportSequenceNumber]
      ,null AS [OverriddenCreatedOn]
      ,0 AS [TimeZoneRuleVersionNumber]
      ,null AS [UTCConversionTimeZoneCode]

FROM [EXISTINGDB].[dbo].[ClientRegions]

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s