This routine uses a series of SQL statements to copy the fields in each table from one Control database to a second database. A few fields are looked up by name and must be manually created in the target database before the import is done.

The basic SQL statement flow is:

1. Temporary Tables are Created for all of the Tables to be copied

1. Unsupported fields are blanked out.

1. New IDs are generated for the new database and the existing IDs mapped to those new values.

1. Those tables are updated with new referenced IDs for any linked tables that are not being imported

1. The temporary tables are inserted into the new database

The following information is not imported in the process:

• UDF information for Companies
• UDF information for Contacts
• Credit Card Information (since each store has it's own unique encryption keys, this can't be brought over)
• Shipping Carrier Information
• Pricing Plans
• Tax Lookup by Postal Code
• Contact Login Information for Control Cloud Connections

The following tables are copied (intact except for the references in the next section)

• Account
• AccountContact
• Phone
• Address
• AddressLink
• PricingLevel
• Promotion

The following UDF tables are partially copied. A record is created for each Customer and Contact, but none of the UDF values are copied nor are the UDF fields created.

• AccountUserField
• AccountContactUserField

The following tables are referenced in the copying and the indicated text fields must be found and match the spelling in the original system exactly:

• Employee First Name and Last Name must match exactly. Any value not found in the destination will be reset to the House Account.
• PaymentTerms Name must match exactly. Any value not found in the destination will be reset to the default.
• Industry Name must match exactly. Any value not found in the destination will be cleared.
• Origin Name must match exactly. Any value not found in the destination will be cleared.

The following fields are NOT copied or referenced. Any use of these fields are reset.

• Pricing Plan. Always reset to default.
• Address Level Tax Class Lookup. Always reset to none.
• Shipping Method on the Contact. Always reset to none.

This should only be used by an experienced consultant and all of the limitations understood. If this query is used on a database in use, it will almost certainly fail and result in bad customer and contact data in the system.

=Steps=

code_formatsql

– You need to make sure you are running this from the Source Database

–

use Historical_StoreData;

– step 0, set initial values

1. - Set the target database

DECLARE @TargetDatabase varchar(64) = 'StoreData-Dest';

1. - set @Debug to 1 to display intermediate steps

DECLARE @debug bit = 0;

1. - set to @RollBackImport to 1 to rollback the import and not commit it (helpful while testing)

DECLARE @RollBackImport bit = 0;

1. - other working variables

DECLARE @sql nvarchar(2048);

DECLARE @ParamDefinition nvarchar(255);

DECLARE @dt smalldatetime = getdate();

DECLARE @MaxCurrentID INT; 

– step 1, select the IDs accounts your want to import into this temp table

1. - table of Account IDs

IF OBJECT_ID('tempdb..#AIDs') IS NOT NULL DROP TABLE #AIDs;

create table #AIDs (ID int primary key);

insert into #AIDs

	select ID from Account

	where ID > 1001  -- all customers past the test company

	  and IsActive = 1

	  -- and have orders within the last 5 years

	  and ID in (

              select AccountID 

              from TransHeader 

              where OrderCreatedDate > DateAdd(Year, -5, GetDate())

              or EstimateCreatedDate > DateAdd(Year, -1, GetDate())

              )

	order by id 

	 ;

1. - table of account contact IDs

IF OBJECT_ID('tempdb..#ACIDs') IS NOT NULL DROP TABLE #ACIDs;

create table #ACIDs (ID int primary key);

insert into #ACIDs

	select AC.ID from AccountContact AC join #AIDs I on AC.AccountID = I.ID

– Step 2, do some clean up

IF OBJECT_ID('tempdb..#AccountTemp')        IS NOT NULL DROP TABLE #AccountTemp;

IF OBJECT_ID('tempdb..#AccountContactTemp') IS NOT NULL DROP TABLE #AccountContactTemp;

IF OBJECT_ID('tempdb..#AddressTemp')        IS NOT NULL DROP TABLE #AddressTemp;

IF OBJECT_ID('tempdb..#AddressLinkTemp')    IS NOT NULL DROP TABLE #AddressLinkTemp;

IF OBJECT_ID('tempdb..#PhoneNumberTemp')    IS NOT NULL DROP TABLE #PhoneNumberTemp;

IF OBJECT_ID('tempdb..#PromotionTemp')      IS NOT NULL DROP TABLE #PromotionTemp;

IF OBJECT_ID('tempdb..#PricingLevelTemp')   IS NOT NULL DROP TABLE #PricingLevelTemp;

IF OBJECT_ID('tempdb..#EmployeeMap')        IS NOT NULL DROP TABLE #EmployeeMap;

IF OBJECT_ID('tempdb..#PaymentTermsMap')    IS NOT NULL DROP TABLE #PaymentTermsMap;

IF OBJECT_ID('tempdb..#TaxClassMap')        IS NOT NULL DROP TABLE #TaxClassMap;

IF OBJECT_ID('tempdb..#IndustryMap')        IS NOT NULL DROP TABLE #IndustryMap;

IF OBJECT_ID('tempdb..#OriginMap')          IS NOT NULL DROP TABLE #OriginMap;

IF OBJECT_ID('tempdb..#AccountMap')         IS NOT NULL DROP TABLE #AccountMap;

IF OBJECT_ID('tempdb..#AccountContactMap')  IS NOT NULL DROP TABLE #AccountContactMap;

IF OBJECT_ID('tempdb..#AddressMap')         IS NOT NULL DROP TABLE #AddressMap;

IF OBJECT_ID('tempdb..#AddressLinkMap')     IS NOT NULL DROP TABLE #AddressLinkMap;

IF OBJECT_ID('tempdb..#PhoneNumberMap')     IS NOT NULL DROP TABLE #PhoneNumberMap;

– Step 3, create temp tables with the data

1. - Account Table

SELECT A.*

INTO #AccountTemp

FROM Account A

JOIN #AIDs I on A.ID = I.ID

;

IF (@debug=1) SELECT TOP 5 * FROM #AccountTemp;

1. - AccountContact Table

SELECT AC.*

INTO #AccountContactTemp

FROM AccountContact AC

JOIN #ACIDs I on AC.ID = I.ID

;

IF (@debug=1) SELECT TOP 5 * FROM #AccountContactTemp;

1. - Address Link Table

SELECT ADL.*

INTO #AddressLinkTemp

FROM AddressLink ADL

LEFT JOIN #AIDs  A  on ADL.ParentID = A.ID  and ADL.ParentClassTypeID = 2000

LEFT JOIN #ACIDs AC on ADL.ParentID = AC.ID and ADL.ParentClassTypeID = 3000

WHERE (A.ID IS NOT NULL or AC.ID IS NOT NULL)

;

IF (@debug=1) SELECT TOP 5 * FROM #AddressLinkTemp;

1. - Address Table

SELECT DISTINCT AD.*

INTO #AddressTemp

FROM Address AD

JOIN #AddressLinkTemp ADL on AD.ID = ADL.AddressID

;

IF (@debug=1) SELECT TOP 5 * FROM #AddressTemp;

1. - Phone Number Table

SELECT P.*

INTO #PhoneNumberTemp

FROM PhoneNumber P

LEFT JOIN #AIDs  A  on P.ParentID = A.ID  and P.ParentClassTypeID = 2000

LEFT JOIN #ACIDs AC on P.ParentID = AC.ID and P.ParentClassTypeID = 3000

WHERE (A.ID IS NOT NULL or AC.ID IS NOT NULL)

;

IF (@debug=1) SELECT TOP 5 * FROM #PhoneNumberTemp;

1. - Promotion Table

SELECT PR.*

INTO #PromotionTemp

FROM Promotion PR

JOIN Account A on A.PromotionID = PR.ID

JOIN #AIDs  AI on AI.ID = A.ID

;

IF (@debug=1) SELECT TOP 5 * FROM #PromotionTemp;

1. - Pricing Level Table

SELECT PL.*

INTO #PricingLevelTemp

FROM PricingLevel PL

JOIN Account A on A.PricingLevelID = PL.ID

JOIN #AIDs  AI on AI.ID = A.ID

;

IF (@debug=1) SELECT TOP 5 * FROM #PricingLevelTemp;

– Step 4 - Create Map Tables for Fields not Copied

1. - Create Employee Map

SELECT DISTINCT T.OldID, FirstName, LastName, convert(int, NULL) as NewID

INTO #EmployeeMap

FROM

(

	select SalespersonID1 as OldID from Account where SalesPersonID1 > 999

	union

	select SalespersonID2 as OldID from Account where SalesPersonID1 > 999

	union

	select SalespersonID3 as OldID from Account where SalesPersonID1 > 999

	union

	select SalespersonID1 as OldID from AccountContact where SalesPersonID1 > 999

	union

	select SalespersonID2 as OldID from AccountContact where SalesPersonID1 > 999

	union

	select SalespersonID3 as OldID from AccountContact where SalesPersonID1 > 999

) T

join Employee E on E.ID = T.OldID

;

SET @sql = 'Update EM

			Set NewID = E2.ID

			From #EmployeeMap EM

			Join ['+@TargetDatabase+'].dbo.Employee E2 

			  on EM.LastName = E2.LastName and EM.FirstName = E2.FirstName'

;

exec (@sql)

;

IF (@debug=1) SELECT TOP 5 * FROM #EmployeeMap;

1. - Create Payment Terms Map

SELECT DISTINCT PT.ID as OldID, PT.TermsName as TermsName, convert(int, NULL) as NewID

INTO #PaymentTermsMap

FROM Account A

join #AIDs I on A.ID = I.ID

join PaymentTerms PT on PT.ID = A.PaymentTermsID

;

SET @sql = 'Update PTM

			Set NewID = PT2.ID

			From #PaymentTermsMap PTM

			Join ['+@TargetDatabase+'].dbo.PaymentTerms PT2 

			  on PTM.TermsName = PT2.TermsName'

;

exec (@sql)

;

IF (@debug=1) SELECT TOP 5 * FROM #PaymentTermsMap;

1. - Create Tax Class Map

SELECT DISTINCT TC.ID as OldID, TC.TaxClassName as TaxClassName, convert(int, NULL) as NewID

INTO #TaxClassMap

FROM Account A

join #AIDs I on A.ID = I.ID

join TaxClass TC on TC.ID = A.TaxClassID

;

SET @sql = 'Update TCM

			Set NewID = TC2.ID

			From #TaxClassMap TCM

			Join ['+@TargetDatabase+'].dbo.TaxClass TC2 

			  on TCM.TaxClassName = TC2.TaxClassName'

;

exec (@sql)

;

IF (@debug=1) SELECT TOP 5 * FROM #TaxClassMap;

;

1. - Create Indusry Map

SELECT DISTINCT M.ID as OldID, M.ItemName, convert(int, NULL) as NewID

INTO #IndustryMap

FROM MarketingListItem M

join #AccountTemp A on A.IndustryID = M.ID

WHERE M.MarketingListID = 10

;

SET @sql = 'Update IM

			Set NewID = TC2.ID

			From #IndustryMap IM

			Join ['+@TargetDatabase+'].dbo.MarketingListItem TC2 

			  on IM.ItemName = TC2.ItemName

              Where TC2.MarketingListID = 10'

;

exec (@sql)

;

IF (@debug=1) SELECT TOP 5 * FROM #IndustryMap;

;

1. - Create Origin Map

SELECT DISTINCT M.ID as OldID, M.ItemName, convert(int, NULL) as NewID

INTO #OriginMap

FROM MarketingListItem M

join #AccountTemp A on A.OriginID = M.ID

WHERE M.MarketingListID = 11

;

SET @sql = 'Update OM

			Set NewID = TC2.ID

			From #OriginMap OM

			Join ['+@TargetDatabase+'].dbo.MarketingListItem TC2 

			  on OM.ItemName = TC2.ItemName

              Where TC2.MarketingListID = 11'

;

exec (@sql)

;

IF (@debug=1) SELECT TOP 5 * FROM #OriginMap;

;

1. - Create ID Map for Account

1. - Find the last address used in the target database

SET @SQL = 'Select @ResultOut = Max(ID) from ['+@TargetDatabase+'].dbo.Account;';

SET @ParamDefinition = '@ResultOut int OUTPUT';

EXEC sp_executesql @SQL, @ParamDefinition, @ResultOut=@MaxCurrentID OUTPUT;

  IF (COALESCE(@MaxCurrentID,0) < 10000) SET @MaxCurrentID = 10000;

SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by CompanyName) AS NewID

  INTO  #AccountMap

  FROM #AccountTemp

  ORDER BY CompanyName

1. - Create ID Map for AccountContact

1. - Find the last address used in the target database

SET @SQL = 'Select @ResultOut = Max(ID) from ['+@TargetDatabase+'].dbo.AccountContact;';

SET @ParamDefinition = '@ResultOut int OUTPUT';

EXEC sp_executesql @SQL, @ParamDefinition, @ResultOut=@MaxCurrentID OUTPUT;

  IF (COALESCE(@MaxCurrentID,0) < 10000) SET @MaxCurrentID = 10000;

SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY ID) AS NewID

  INTO  #AccountContactMap

  FROM #AccountContactTemp

1. - Create ID Map for Address

1. - Find the last address used in the target database

SET @SQL = 'Select @ResultOut = Max(ID) from ['+@TargetDatabase+'].dbo.Address;';

SET @ParamDefinition = '@ResultOut int OUTPUT';

EXEC sp_executesql @SQL, @ParamDefinition, @ResultOut=@MaxCurrentID OUTPUT;

  IF (COALESCE(@MaxCurrentID,0) < 10000) SET @MaxCurrentID = 10000;

SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID

  INTO  #AddressMap

  FROM #AddressTemp

1. - Create ID Map for AddressLink

1. - Find the last address used in the target database

SET @SQL = 'Select @ResultOut = Max(ID) from ['+@TargetDatabase+'].dbo.AddressLink;';

SET @ParamDefinition = '@ResultOut int OUTPUT';

EXEC sp_executesql @SQL, @ParamDefinition, @ResultOut=@MaxCurrentID OUTPUT;

  IF (COALESCE(@MaxCurrentID,0) < 10000) SET @MaxCurrentID = 10000;

SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID

  INTO  #AddressLinkMap

  FROM #AddressLinkTemp

1. - Create ID Map for Phone

1. - Find the last phone used in the target database

SET @SQL = 'Select @ResultOut = Max(ID) from ['+@TargetDatabase+'].dbo.PhoneNumber;';

SET @ParamDefinition = '@ResultOut int OUTPUT';

EXEC sp_executesql @SQL, @ParamDefinition, @ResultOut=@MaxCurrentID OUTPUT;

  IF (COALESCE(@MaxCurrentID,0) < 10000) SET @MaxCurrentID = 10000;

SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID

  INTO  #PhoneNumberMap

  FROM #PhoneNumberTemp 

– Step 5. Update the Temp Tables with the new IDs for the Mapped Tables

  IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID, AccountingContactID, * from #AccountTemp;

update #AccountTemp

set ID             = (select NewID from #AccountMap      where OldID = ID),

      ModifiedByUser = 'Import', ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, DateImported = @dt,

	PrimaryContactID    = (select NewID from #AccountContactMap where OldID = PrimaryContactID),

	AccountingContactID = (select NewID from #AccountContactMap where OldID = AccountingContactID),

      SalesPersonID1 = (select NewID from #EmployeeMap     where OldID = SalesPersonID1),

	SalesPersonID2 = (select NewID from #EmployeeMap     where OldID = SalesPersonID2),

	SalesPersonID3 = (select NewID from #EmployeeMap     where OldID = SalesPersonID3),

	PaymentTermsID = (select NewID from #PaymentTermsMap where OldID = PaymentTermsID),

	TaxClassID     = (select NewID from #TaxClassMap     where OldID = TaxClassID),

	IndustryID     = (select NewID from #IndustryMap     where OldID = IndustryID),

	OriginID       = (select NewID from #OriginMap     where OldID = OriginID),

      BillingAddressID  = (select NewID from #AddressMap   where OldID = BillingAddressID),

      ShippingAddressID = (select NewID from #AddressMap   where OldID = ShippingAddressID),

      MainPhoneNumberID = (select NewID from #PhoneNumberMap     where OldID = MainPhoneNumberID),

      MainFaxNumberID   = (select NewID from #PhoneNumberMap     where OldID = MainFaxNumberID)

;

  IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID, AccountingContactID, * from #AccountTemp;

  IF (@debug=1) SELECT TOP 10 ID, AccountID, * from #AccountContactTemp;

update #AccountContactTemp

set ID             = (select NewID from #AccountContactMap where OldID = ID),

      ModifiedByUser = 'Import', ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 

	CCCSCustomerGuid = NULL, 

	UserID = NULL, 		 

	UseShippingAccountInfo = 0, DefaultShippingCarrierID = NULL, ShippingMethodLinksXML = NULL, 

	AccountID      = (select NewID from #AccountMap      where OldID = AccountID),

	SalesPersonID1 = (select NewID from #EmployeeMap     where OldID = SalesPersonID1),

	SalesPersonID2 = (select NewID from #EmployeeMap     where OldID = SalesPersonID2),

	SalesPersonID3 = (select NewID from #EmployeeMap     where OldID = SalesPersonID3),

      BillingAddressID  = (select NewID from #AddressMap   where OldID = BillingAddressID),

      ShippingAddressID = (select NewID from #AddressMap   where OldID = ShippingAddressID),

      PaymentAddressID  = (select NewID from #AddressMap   where OldID = PaymentAddressID),

      PaymentAddressLinkID  = (select NewID from #AddressLinkMap   where OldID = PaymentAddressLinkID),

      MainPhoneNumberID = (select NewID from #PhoneNumberMap     where OldID = MainPhoneNumberID),

      MainFaxNumberID   = (select NewID from #PhoneNumberMap     where OldID = MainFaxNumberID)

;

  IF (@debug=1) SELECT TOP 10 ID, AccountID, * from #AccountContactTemp;

update #AddressTemp

set ID             = (select NewID from #AddressMap    where OldID = ID),

      ModifiedByUser = 'Import', ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 

	TaxClassID = NULL

;

update #AddressLinkTemp

set ID             = (select NewID from #AddressLinkMap    where OldID = ID),

      ModifiedByUser = 'Import', ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 

      AddressID      = (select NewID from #AddressMap    where OldID = AddressID),

      ParentID       = CASE WHEN ParentClassTypeID = 2000 

                            THEN (select NewID from #AccountMap where OldID = ParentID)

                            WHEN ParentClassTypeID = 3000 

                            THEN (select NewID from #AccountContactMap where OldID = ParentID)

                            ELSE ParentID

                        END

;

update #PhoneNumberTemp

set ID             = (select NewID from #PhoneNumberMap    where OldID = ID),

      ModifiedByUser = 'Import', ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,

      ParentID       = CASE WHEN ParentClassTypeID = 2000 

                            THEN (select NewID from #AccountMap where OldID = ParentID)

                            WHEN ParentClassTypeID = 3000 

                            THEN (select NewID from #AccountContactMap where OldID = ParentID)

                            ELSE ParentID

                        END

;

– Step 6. Insert the Temp Tables

BEGIN TRANSACTION

BEGIN TRY

1. - insert the primary data

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.Account SELECT * FROM #AccountTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.AccountContact SELECT * FROM #AccountContactTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.Address SELECT * FROM #AddressTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.AddressLink SELECT * FROM #AddressLinkTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.PhoneNumber SELECT * FROM #PhoneNumberTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

1. - now create the stub entries for the UDF tables

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.AccountUserField 

				(ID, StoreID, ClassTypeID, SeqID, IsSystem, IsActive)

				SELECT ID, -1, 2001, 0, 0, 1

				FROM #AccountTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	set @sql = 'INSERT INTO ['+@TargetDatabase+'].dbo.AccountContactUserField 

				(ID, StoreID, ClassTypeID, SeqID, IsSystem, IsActive)

				SELECT ID, -1, 3001, 0, 0, 1

				FROM #AccountContactTemp';

	EXEC (@sql);  IF (@debug = 1) PRINT @sql;

	IF (@RollBackImport = 1) 

		ROLLBACK TRANSACTION 

	ELSE

		COMMIT TRANSACTION

	;

END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION;

	declare @ErrorMessage    VARCHAR(2048);

	declare @ErrorSeverity   INT;

	declare @ErrorState      INT;

      SELECT @ErrorMessage = ERROR_MESSAGE(),

             @ErrorSeverity = ERROR_SEVERITY(),

             @ErrorState = ERROR_STATE();

1. - Use RAISERROR inside the CATCH block to return

1. - error information about the original error that

1. - caused execution to jump to the CATCH block.

      RAISERROR (@ErrorMessage, -- Message text.

                 @ErrorSeverity, -- Severity.

                 @ErrorState -- State.

                 );

  END CATCH;

code

Contributor: Cyrious Consulting

Date: 7/2016

Version: Control 5.7

• Backlinks include_pagepage_componentbacklinks