This procedure documents how to import the customers from an existing Control database into a new Control database. The destination Control database MUST HAVE NOT CUSTOMERS. Since the IDs of the existing customer records are not regenerated, this routine can not be used to import customers into a database with existing customers in it.
Approach
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:
- Temporary Tables are Created for all of the Tables to be copied
- Unsupported fields are blanked out.
- New IDs are generated for the new database and the existing IDs mapped to those new values.
- Those tables are updated with new referenced IDs for any linked tables that are not being imported
- 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
Tables Affected
Copied
The following tables are copied (intact except for the references in the next section)
- Account
- AccountContact
- Phone
- Address
- AddressLink
- PricingLevel
- Promotion
Partially Copied
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
Referenced By Name
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.
Not Copied or Supported
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.
Cautions
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
-- You need to make sure you are running this from the Source Database -- USE Historical_StoreData; -- step 0, set initial values -- Set the target database DECLARE @TargetDatabase VARCHAR(64) = 'StoreData-Dest'; -- set @Debug to 1 to display intermediate steps DECLARE @debug bit = 0; -- set to @RollBackImport to 1 to rollback the import and not commit it (helpful while testing) DECLARE @RollBackImport bit = 0; -- 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 -- 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 ; -- 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 -- 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; -- 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; -- 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; -- 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; -- 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; -- 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; -- 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 -- 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; -- 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; -- 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; ; -- 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; ; -- 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; ; -- Create ID Map for Account -- 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 -- Create ID Map for AccountContact -- 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 -- Create ID Map for Address -- 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 -- Create ID Map for AddressLink -- 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 -- Create ID Map for Phone -- 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 -- 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; -- 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(); -- Use RAISERROR inside the CATCH block to return -- error information about the original error that -- caused execution to jump to the CATCH block. RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ); END CATCH;
Source
Contributor: Cyrious Consulting
Date: 7/2016
Version: Control 5.7