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
2. Unsupported fields are blanked out.
3. New IDs are generated for the new database and the existing IDs mapped to those new values.
4. Those tables are updated with new referenced IDs for any linked tables that are not being imported
5. 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.

-- 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;

Contributor: Cyrious Consulting

Date: 7/2016

Version: Control 5.7