Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control-to-control_customer_imports [2019/01/30 09:23]
127.0.0.1 external edit
control-to-control_customer_imports [2019/02/07 10:28] (current)
kcifreo
Line 1: Line 1:
 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. 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 ====== ====== 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. 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:
  
- 
-The basic SQL statement flow is: 
   - Temporary Tables are Created for all of the Tables to be copied   - Temporary Tables are Created for all of the Tables to be copied
   - Unsupported fields are blanked out.   - Unsupported fields are blanked out.
Line 18: Line 13:
   - The temporary tables are inserted into the new database   - The temporary tables are inserted into the new database
  
 +The following information is __not__ ​ imported in the process:
  
- 
-The following information is __not__ imported in the process: 
   * UDF information for Companies   * UDF information for Companies
   * UDF information for Contacts   * UDF information for Contacts
Line 28: Line 22:
   * Tax Lookup by Postal Code   * Tax Lookup by Postal Code
   * Contact Login Information for Control Cloud Connections   * Contact Login Information for Control Cloud Connections
- 
- 
  
 ====== Tables Affected ====== ====== Tables Affected ======
- 
- 
  
 ===== Copied ===== ===== Copied =====
  
 +The following tables are copied (intact except for the references in the next section)
  
- 
-The following tables are copied (intact except for the references in the next section) 
   * Account   * Account
   * AccountContact   * AccountContact
Line 47: Line 36:
   * PricingLevel   * PricingLevel
   * Promotion   * Promotion
- 
- 
  
 ===== Partially Copied ===== ===== 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.
  
- 
-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   * AccountUserField
   * AccountContactUserField   * AccountContactUserField
- 
- 
  
 ===== Referenced By Name ===== ===== 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:
  
- 
-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.   * 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.   * 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.   * 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.   * Origin Name must match exactly. Any value not found in the destination will be cleared.
- 
- 
  
 ===== Not Copied or Supported ===== ===== Not Copied or Supported =====
  
 +The following fields are NOT copied or referenced. Any use of these fields are reset.
  
- 
-The following fields are NOT copied or referenced. Any use of these fields are reset. 
   * Pricing Plan. Always reset to default.   * Pricing Plan. Always reset to default.
   * Address Level Tax Class Lookup. Always reset to none.   * Address Level Tax Class Lookup. Always reset to none.
   * Shipping Method on the Contact. Always reset to none.   * Shipping Method on the Contact. Always reset to none.
- 
- 
  
 ====== Cautions ====== ====== 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. 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 ====
- +
-=Steps= +
- +
  
 <code sql> <code sql>
 -- You need to make sure you are running this from the Source Database -- You need to make sure you are running this from the Source Database
 -- --
-use Historical_StoreData;​+USE Historical_StoreData;​
 -- step 0, set initial values -- step 0, set initial values
- -- Set the target database +    ​-- Set the target database 
- DECLARE @TargetDatabase ​varchar(64) = '​StoreData-Dest';​ +    DECLARE @TargetDatabase ​VARCHAR(64) = '​StoreData-Dest';​ 
- -- set @Debug to 1 to display intermediate steps +    -- set @Debug to 1 to display intermediate steps 
- DECLARE @debug bit = 0; +    DECLARE @debug bit = 0; 
- -- set to @RollBackImport to 1 to rollback the import and not commit it (helpful while testing) +    -- set to @RollBackImport to 1 to rollback the import and not commit it (helpful while testing) 
- DECLARE @RollBackImport bit = 0; +    DECLARE @RollBackImport bit = 0; 
- -- other working variables +    -- other working variables 
- DECLARE @sql nvarchar(2048);​ +    DECLARE @SQL nvarchar(2048);​ 
- DECLARE @ParamDefinition nvarchar(255);​ +    DECLARE @ParamDefinition nvarchar(255);​ 
- DECLARE @dt smalldatetime = getdate();​ +    DECLARE @dt smalldatetime = getdate();​ 
- DECLARE @MaxCurrentID INT; +    DECLARE @MaxCurrentID INT;
 -- step 1, select the IDs accounts your want to import into this temp table -- step 1, select the IDs accounts your want to import into this temp table
- -- table of Account IDs +    ​-- table of Account IDs 
- IF OBJECT_ID('​tempdb..#​AIDs'​) IS NOT NULL DROP TABLE #AIDs; +    IF OBJECT_ID('​tempdb..#​AIDs'​) IS NOT NULL DROP TABLE #AIDs; 
- create table #AIDs (ID int primary key); +    ​CREATE TABLE #AIDs (ID INT PRIMARY KEY); 
- insert into #AIDs +    ​INSERT INTO #AIDs 
- select ​ID from Account +        ​SELECT ​ID FROM Account 
- where ​ID > 1001  -- all customers past the test company +        ​WHERE ​ID > 1001  -- all customers past the test company 
-   and IsActive = 1 +          ​AND ​IsActive = 1 
-   ​-- and have orders within the last 5 years +          -- and have orders within the last 5 years 
-   and ID in +          ​AND ​ID IN 
-                ​select ​AccountID  +                ​SELECT ​AccountID 
-                ​from TransHeader  +                ​FROM TransHeader 
-                ​where OrderCreatedDate > DateAdd(Year, -5, GetDate()) +                ​WHERE OrderCreatedDate > DateAdd(YEAR, -5, GetDate()) 
-                ​or EstimateCreatedDate > DateAdd(Year, -1, GetDate()) +                ​OR EstimateCreatedDate > DateAdd(YEAR, -1, GetDate()) 
-                +
                 )                 )
- order by id  +        ORDER BY id 
-  +         ​
- -- table of account contact IDs +    -- table of account contact IDs 
- IF OBJECT_ID('​tempdb..#​ACIDs'​) IS NOT NULL DROP TABLE #ACIDs; +    IF OBJECT_ID('​tempdb..#​ACIDs'​) IS NOT NULL DROP TABLE #ACIDs; 
- create table #ACIDs (ID int primary key); +    ​CREATE TABLE #ACIDs (ID INT PRIMARY KEY); 
- insert into #ACIDs +    ​INSERT INTO #ACIDs 
- select ​AC.ID from AccountContact AC join #AIDs I on AC.AccountID = I.ID+        ​SELECT ​AC.ID FROM AccountContact AC JOIN #AIDs I ON AC.AccountID = I.ID
 -- Step 2, do some clean up -- Step 2, do some clean up
- IF OBJECT_ID('​tempdb..#​AccountTemp'​) ​       IS NOT NULL DROP TABLE #​AccountTemp;​ +    ​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..#​AccountContactTemp'​) IS NOT NULL DROP TABLE #​AccountContactTemp;​ 
- IF OBJECT_ID('​tempdb..#​AddressTemp'​) ​       IS NOT NULL DROP TABLE #​AddressTemp;​ +    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..#​AddressLinkTemp'​) ​   IS NOT NULL DROP TABLE #​AddressLinkTemp;​ 
- IF OBJECT_ID('​tempdb..#​PhoneNumberTemp'​) ​   IS NOT NULL DROP TABLE #​PhoneNumberTemp;​ +    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..#​PromotionTemp'​) ​     IS NOT NULL DROP TABLE #​PromotionTemp;​ 
- IF OBJECT_ID('​tempdb..#​PricingLevelTemp'​) ​  IS NOT NULL DROP TABLE #​PricingLevelTemp;​ +    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..#​EmployeeMap'​) ​       IS NOT NULL DROP TABLE #​EmployeeMap;​ 
- IF OBJECT_ID('​tempdb..#​PaymentTermsMap'​) ​   IS NOT NULL DROP TABLE #​PaymentTermsMap;​ +    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..#​TaxClassMap'​) ​       IS NOT NULL DROP TABLE #​TaxClassMap;​ 
- IF OBJECT_ID('​tempdb..#​IndustryMap'​) ​       IS NOT NULL DROP TABLE #​IndustryMap;​ +    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..#​OriginMap'​) ​         IS NOT NULL DROP TABLE #​OriginMap;​ 
- IF OBJECT_ID('​tempdb..#​AccountMap'​) ​        IS NOT NULL DROP TABLE #​AccountMap;​ +    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..#​AccountContactMap'​) ​ IS NOT NULL DROP TABLE #​AccountContactMap;​ 
- IF OBJECT_ID('​tempdb..#​AddressMap'​) ​        IS NOT NULL DROP TABLE #​AddressMap;​ +    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..#​AddressLinkMap'​) ​    IS NOT NULL DROP TABLE #​AddressLinkMap;​ 
- IF OBJECT_ID('​tempdb..#​PhoneNumberMap'​) ​    IS NOT NULL DROP TABLE #​PhoneNumberMap;​+    IF OBJECT_ID('​tempdb..#​PhoneNumberMap'​) ​    IS NOT NULL DROP TABLE #​PhoneNumberMap;​
 -- Step 3, create temp tables with the data -- Step 3, create temp tables with the data
- -- Account Table +    ​-- Account Table 
- SELECT A.* +    SELECT A.* 
- INTO #​AccountTemp +    INTO #​AccountTemp 
- FROM Account A +    FROM Account A 
- JOIN #AIDs I on A.ID = I.ID +    JOIN #AIDs I ON A.ID = I.ID 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​AccountTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​AccountTemp;​ 
- -- AccountContact Table +    -- AccountContact Table 
- SELECT AC.* +    SELECT AC.* 
- INTO #​AccountContactTemp +    INTO #​AccountContactTemp 
- FROM AccountContact AC +    FROM AccountContact AC 
- JOIN #ACIDs I on AC.ID = I.ID +    JOIN #ACIDs I ON AC.ID = I.ID 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​AccountContactTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​AccountContactTemp;​ 
- -- Address Link Table +    -- Address Link Table 
- SELECT ADL.* +    SELECT ADL.* 
- INTO #​AddressLinkTemp +    INTO #​AddressLinkTemp 
- FROM AddressLink ADL +    FROM AddressLink ADL 
- LEFT JOIN #AIDs  A  ​on ADL.ParentID = A.ID  ​and ADL.ParentClassTypeID = 2000 +    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 +    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) +    WHERE (A.ID IS NOT NULL OR AC.ID IS NOT NULL) 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​AddressLinkTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​AddressLinkTemp;​ 
- -- Address Table +    -- Address Table 
- SELECT DISTINCT AD.* +    SELECT DISTINCT AD.* 
- INTO #​AddressTemp +    INTO #​AddressTemp 
- FROM Address AD +    FROM Address AD 
- JOIN #​AddressLinkTemp ADL on AD.ID = ADL.AddressID +    JOIN #​AddressLinkTemp ADL ON AD.ID = ADL.AddressID 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​AddressTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​AddressTemp;​ 
- -- Phone Number Table +    -- Phone Number Table 
- SELECT P.* +    SELECT P.* 
- INTO #​PhoneNumberTemp +    INTO #​PhoneNumberTemp 
- FROM PhoneNumber P +    FROM PhoneNumber P 
- LEFT JOIN #AIDs  A  ​on P.ParentID = A.ID  ​and P.ParentClassTypeID = 2000 +    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 +    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) +    WHERE (A.ID IS NOT NULL OR AC.ID IS NOT NULL) 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​PhoneNumberTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​PhoneNumberTemp;​ 
- -- Promotion Table +    -- Promotion Table 
- SELECT PR.* +    SELECT PR.* 
- INTO #​PromotionTemp +    INTO #​PromotionTemp 
- FROM Promotion PR +    FROM Promotion PR 
- JOIN Account A on A.PromotionID = PR.ID +    JOIN Account A ON A.PromotionID = PR.ID 
- JOIN #AIDs  AI on AI.ID = A.ID +    JOIN #AIDs  AI ON AI.ID = A.ID 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​PromotionTemp;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​PromotionTemp;​ 
- -- Pricing Level Table +    -- Pricing Level Table 
- SELECT PL.* +    SELECT PL.* 
- INTO #​PricingLevelTemp +    INTO #​PricingLevelTemp 
- FROM PricingLevel PL +    FROM PricingLevel PL 
- JOIN Account A on A.PricingLevelID = PL.ID +    JOIN Account A ON A.PricingLevelID = PL.ID 
- JOIN #AIDs  AI on AI.ID = A.ID +    JOIN #AIDs  AI ON AI.ID = A.ID 
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​PricingLevelTemp;​+    IF (@debug=1) SELECT TOP 5 * FROM #​PricingLevelTemp;​
 -- Step 4 - Create Map Tables for Fields not Copied -- Step 4 - Create Map Tables for Fields not Copied
- -- Create Employee Map +    ​-- Create Employee Map 
- SELECT DISTINCT T.OldID, FirstName, LastName, ​convert(int, NULL) as NewID +    SELECT DISTINCT T.OldID, FirstName, LastName, ​CONVERT(INT, NULL) AS NewID 
- INTO #​EmployeeMap +    INTO #​EmployeeMap 
- FROM +    FROM 
-+    
- select ​SalespersonID1 ​as OldID from Account ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID1 ​AS OldID FROM Account ​WHERE SalesPersonID1 > 999 
- union +        UNION 
- select ​SalespersonID2 ​as OldID from Account ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID2 ​AS OldID FROM Account ​WHERE SalesPersonID1 > 999 
- union +        UNION 
- select ​SalespersonID3 ​as OldID from Account ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID3 ​AS OldID FROM Account ​WHERE SalesPersonID1 > 999 
- union +        UNION 
- select ​SalespersonID1 ​as OldID from AccountContact ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID1 ​AS OldID FROM AccountContact ​WHERE SalesPersonID1 > 999 
- union +        UNION 
- select ​SalespersonID2 ​as OldID from AccountContact ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID2 ​AS OldID FROM AccountContact ​WHERE SalesPersonID1 > 999 
- union +        UNION 
- select ​SalespersonID3 ​as OldID from AccountContact ​where SalesPersonID1 > 999 +        ​SELECT ​SalespersonID3 ​AS OldID FROM AccountContact ​WHERE SalesPersonID1 > 999 
- ) T +    ) T 
- join Employee E on E.ID = T.OldID +    ​JOIN ​Employee E ON E.ID = T.OldID 
-+    
- SET @sql = '​Update EM +    SET @SQL = '​Update EM 
- Set NewID = E2.ID +                Set NewID = E2.ID 
- From #​EmployeeMap EM +                From #​EmployeeMap EM 
- Join ['​+@TargetDatabase+'​].dbo.Employee E2  +                Join ['​+@TargetDatabase+'​].dbo.Employee E2 
-   ​on EM.LastName = E2.LastName and EM.FirstName = E2.FirstName'​ +                  on EM.LastName = E2.LastName and EM.FirstName = E2.FirstName'​ 
-+    
- exec (@sql+    ​EXEC ​(@SQL
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​EmployeeMap;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​EmployeeMap;​ 
- -- Create Payment Terms Map +    -- Create Payment Terms Map 
- SELECT DISTINCT PT.ID as OldID, PT.TermsName ​as TermsName, ​convert(int, NULL) as NewID +    SELECT DISTINCT PT.ID AS OldID, PT.TermsName ​AS TermsName, ​CONVERT(INT, NULL) AS NewID 
- INTO #​PaymentTermsMap +    INTO #​PaymentTermsMap 
- FROM Account A +    FROM Account A 
- join #AIDs I on A.ID = I.ID +    ​JOIN ​#AIDs I ON A.ID = I.ID 
- join PaymentTerms PT on PT.ID = A.PaymentTermsID +    ​JOIN ​PaymentTerms PT ON PT.ID = A.PaymentTermsID 
-+    
- SET @sql = '​Update PTM +    SET @SQL = '​Update PTM 
- Set NewID = PT2.ID +                Set NewID = PT2.ID 
- From #​PaymentTermsMap PTM +                From #​PaymentTermsMap PTM 
- Join ['​+@TargetDatabase+'​].dbo.PaymentTerms PT2  +                Join ['​+@TargetDatabase+'​].dbo.PaymentTerms PT2 
-   ​on PTM.TermsName = PT2.TermsName'​ +                  on PTM.TermsName = PT2.TermsName'​ 
-+    
- exec (@sql+    ​EXEC ​(@SQL
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​PaymentTermsMap;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​PaymentTermsMap;​ 
- -- Create Tax Class Map +    -- Create Tax Class Map 
- SELECT DISTINCT TC.ID as OldID, TC.TaxClassName ​as TaxClassName, ​convert(int, NULL) as NewID +    SELECT DISTINCT TC.ID AS OldID, TC.TaxClassName ​AS TaxClassName, ​CONVERT(INT, NULL) AS NewID 
- INTO #​TaxClassMap +    INTO #​TaxClassMap 
- FROM Account A +    FROM Account A 
- join #AIDs I on A.ID = I.ID +    ​JOIN ​#AIDs I ON A.ID = I.ID 
- join TaxClass TC on TC.ID = A.TaxClassID +    ​JOIN ​TaxClass TC ON TC.ID = A.TaxClassID 
-+    
- SET @sql = '​Update TCM +    SET @SQL = '​Update TCM 
- Set NewID = TC2.ID +                Set NewID = TC2.ID 
- From #​TaxClassMap TCM +                From #​TaxClassMap TCM 
- Join ['​+@TargetDatabase+'​].dbo.TaxClass TC2  +                Join ['​+@TargetDatabase+'​].dbo.TaxClass TC2 
-   ​on TCM.TaxClassName = TC2.TaxClassName'​ +                  on TCM.TaxClassName = TC2.TaxClassName'​ 
-+    
- exec (@sql+    ​EXEC ​(@SQL
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​TaxClassMap;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​TaxClassMap;​ 
-+    
-  + 
- -- Create Indusry Map +    -- Create Indusry Map 
- SELECT DISTINCT M.ID as OldID, M.ItemName, ​convert(int, NULL) as NewID +    SELECT DISTINCT M.ID AS OldID, M.ItemName, ​CONVERT(INT, NULL) AS NewID 
- INTO #​IndustryMap +    INTO #​IndustryMap 
- FROM MarketingListItem M +    FROM MarketingListItem M 
- join #​AccountTemp A on A.IndustryID = M.ID +    ​JOIN ​#​AccountTemp A ON A.IndustryID = M.ID 
- WHERE M.MarketingListID = 10 +    WHERE M.MarketingListID = 10 
-+    
- SET @sql = '​Update IM +    SET @SQL = '​Update IM 
- Set NewID = TC2.ID +                Set NewID = TC2.ID 
- From #​IndustryMap IM +                From #​IndustryMap IM 
- Join ['​+@TargetDatabase+'​].dbo.MarketingListItem TC2  +                Join ['​+@TargetDatabase+'​].dbo.MarketingListItem TC2 
-   ​on IM.ItemName = TC2.ItemName+                  on IM.ItemName = TC2.ItemName
                 Where TC2.MarketingListID = 10'                 Where TC2.MarketingListID = 10'
- +    ​
- exec (@sql+    ​EXEC ​(@SQL
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​IndustryMap;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​IndustryMap;​ 
-+    
-  + 
- -- Create Origin Map +    -- Create Origin Map 
- SELECT DISTINCT M.ID as OldID, M.ItemName, ​convert(int, NULL) as NewID +    SELECT DISTINCT M.ID AS OldID, M.ItemName, ​CONVERT(INT, NULL) AS NewID 
- INTO #​OriginMap +    INTO #​OriginMap 
- FROM MarketingListItem M +    FROM MarketingListItem M 
- join #​AccountTemp A on A.OriginID = M.ID +    ​JOIN ​#​AccountTemp A ON A.OriginID = M.ID 
- WHERE M.MarketingListID = 11 +    WHERE M.MarketingListID = 11 
-+    
- SET @sql = '​Update OM +    SET @SQL = '​Update OM 
- Set NewID = TC2.ID +                Set NewID = TC2.ID 
- From #OriginMap OM +                From #OriginMap OM 
- Join ['​+@TargetDatabase+'​].dbo.MarketingListItem TC2  +                Join ['​+@TargetDatabase+'​].dbo.MarketingListItem TC2 
-   ​on OM.ItemName = TC2.ItemName+                  on OM.ItemName = TC2.ItemName
                 Where TC2.MarketingListID = 11'                 Where TC2.MarketingListID = 11'
- +    ​
- exec (@sql+    ​EXEC ​(@SQL
-+    
- IF (@debug=1) SELECT TOP 5 * FROM #​OriginMap;​ +    IF (@debug=1) SELECT TOP 5 * FROM #​OriginMap;​ 
-+    
- -- Create ID Map for Account +    -- Create ID Map for Account 
-    +
     -- Find the last address used in the target database     -- Find the last address used in the target database
- SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.Account;';​ +    ​SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.Account;';​ 
- SET @ParamDefinition = '​@ResultOut int OUTPUT';​ +    SET @ParamDefinition = '​@ResultOut int OUTPUT';​ 
- EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;+    EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;
     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;
-  + 
- SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by CompanyName) AS NewID+    SELECT ID AS OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY CompanyName) AS NewID
     INTO  #AccountMap     INTO  #AccountMap
     FROM #​AccountTemp     FROM #​AccountTemp
     ORDER BY CompanyName     ORDER BY CompanyName
- -- Create ID Map for AccountContact +    ​-- Create ID Map for AccountContact 
-    +
     -- Find the last address used in the target database     -- Find the last address used in the target database
- SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.AccountContact;';​ +    ​SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.AccountContact;';​ 
- SET @ParamDefinition = '​@ResultOut int OUTPUT';​ +    SET @ParamDefinition = '​@ResultOut int OUTPUT';​ 
- EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;+    EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;
     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;
-  + 
- SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY ID) AS NewID+    SELECT ID AS OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY ID) AS NewID
     INTO  #​AccountContactMap     INTO  #​AccountContactMap
     FROM #​AccountContactTemp     FROM #​AccountContactTemp
- -- Create ID Map for Address +    ​-- Create ID Map for Address 
-    +
     -- Find the last address used in the target database     -- Find the last address used in the target database
- SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.Address;';​ +    ​SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.Address;';​ 
- SET @ParamDefinition = '​@ResultOut int OUTPUT';​ +    SET @ParamDefinition = '​@ResultOut int OUTPUT';​ 
- EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;+    EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;
     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;
-  + 
- SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID+    SELECT ID AS OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY Id) AS NewID
     INTO  #AddressMap     INTO  #AddressMap
     FROM #​AddressTemp     FROM #​AddressTemp
- -- Create ID Map for AddressLink +    ​-- Create ID Map for AddressLink 
-    +
     -- Find the last address used in the target database     -- Find the last address used in the target database
- SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.AddressLink;';​ +    ​SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.AddressLink;';​ 
- SET @ParamDefinition = '​@ResultOut int OUTPUT';​ +    SET @ParamDefinition = '​@ResultOut int OUTPUT';​ 
- EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;+    EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;
     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;
-  + 
- SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID+    SELECT ID AS OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY Id) AS NewID
     INTO  #​AddressLinkMap     INTO  #​AddressLinkMap
     FROM #​AddressLinkTemp     FROM #​AddressLinkTemp
- -- Create ID Map for Phone+    ​-- Create ID Map for Phone
     -- Find the last phone used in the target database     -- Find the last phone used in the target database
- SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.PhoneNumber;';​ +    ​SET @SQL = '​Select @ResultOut = Max(ID) from ['​+@TargetDatabase+'​].dbo.PhoneNumber;';​ 
- SET @ParamDefinition = '​@ResultOut int OUTPUT';​ +    SET @ParamDefinition = '​@ResultOut int OUTPUT';​ 
- EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;+    EXEC sp_executesql @SQL, @ParamDefinition,​ @ResultOut=@MaxCurrentID OUTPUT;
     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;     IF (COALESCE(@MaxCurrentID,​0) < 10000) SET @MaxCurrentID = 10000;
-  + 
- SELECT ID as OldID, @MaxCurrentID + ROW_NUMBER() OVER (Order by Id) AS NewID+    SELECT ID AS OldID, @MaxCurrentID + ROW_NUMBER() OVER (ORDER BY Id) AS NewID
     INTO  #​PhoneNumberMap     INTO  #​PhoneNumberMap
-    FROM #​PhoneNumberTemp ​+    FROM #​PhoneNumberTemp
 -- Step 5. Update the Temp Tables with the new IDs for the Mapped Tables -- 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;​ +    IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID,​ AccountingContactID,​ * FROM #​AccountTemp;​ 
- update ​#​AccountTemp +    ​UPDATE ​#​AccountTemp 
- set ID             = (select ​NewID from #​AccountMap ​     ​where OldID = ID),+    ​SET ​ID             = (SELECT ​NewID FROM #​AccountMap ​     ​WHERE OldID = ID),
         ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, DateImported = @dt,         ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, DateImported = @dt,
- PrimaryContactID ​   = (select ​NewID from #​AccountContactMap ​where OldID = PrimaryContactID),​ +        ​PrimaryContactID ​   = (SELECT ​NewID FROM #​AccountContactMap ​WHERE OldID = PrimaryContactID),​ 
- AccountingContactID = (select ​NewID from #​AccountContactMap ​where OldID = AccountingContactID),​ +        AccountingContactID = (SELECT ​NewID FROM #​AccountContactMap ​WHERE OldID = AccountingContactID),​ 
-        SalesPersonID1 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID1),​ +        SalesPersonID1 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID1),​ 
- SalesPersonID2 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID2),​ +        SalesPersonID2 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID2),​ 
- SalesPersonID3 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID3),​ +        SalesPersonID3 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID3),​ 
- PaymentTermsID = (select ​NewID from #​PaymentTermsMap ​where OldID = PaymentTermsID),​ +        PaymentTermsID = (SELECT ​NewID FROM #​PaymentTermsMap ​WHERE OldID = PaymentTermsID),​ 
- TaxClassID ​    = (select ​NewID from #​TaxClassMap ​    where OldID = TaxClassID),​ +        TaxClassID ​    = (SELECT ​NewID FROM #​TaxClassMap ​    WHERE OldID = TaxClassID),​ 
- IndustryID ​    = (select ​NewID from #​IndustryMap ​    where OldID = IndustryID),​ +        IndustryID ​    = (SELECT ​NewID FROM #​IndustryMap ​    WHERE OldID = IndustryID),​ 
- OriginID ​      = (select ​NewID from #​OriginMap ​    where OldID = OriginID),​ +        OriginID ​      = (SELECT ​NewID FROM #​OriginMap ​    WHERE OldID = OriginID),​ 
-        BillingAddressID ​ = (select ​NewID from #​AddressMap ​  where OldID = BillingAddressID),​ +        BillingAddressID ​ = (SELECT ​NewID FROM #​AddressMap ​  WHERE OldID = BillingAddressID),​ 
-        ShippingAddressID = (select ​NewID from #​AddressMap ​  where OldID = ShippingAddressID),​ +        ShippingAddressID = (SELECT ​NewID FROM #​AddressMap ​  WHERE OldID = ShippingAddressID),​ 
-        MainPhoneNumberID = (select ​NewID from #​PhoneNumberMap ​    where OldID = MainPhoneNumberID),​ +        MainPhoneNumberID = (SELECT ​NewID FROM #​PhoneNumberMap ​    WHERE OldID = MainPhoneNumberID),​ 
-        MainFaxNumberID ​  = (select ​NewID from #​PhoneNumberMap ​    where OldID = MainFaxNumberID) +        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, PrimaryContactID,​ AccountingContactID,​ * FROM #​AccountTemp;​ 
-    IF (@debug=1) SELECT TOP 10 ID, AccountID, * from #​AccountContactTemp;​ +    IF (@debug=1) SELECT TOP 10 ID, AccountID, * FROM #​AccountContactTemp;​ 
- update ​#​AccountContactTemp +    ​UPDATE ​#​AccountContactTemp 
- set ID             = (select ​NewID from #​AccountContactMap ​where OldID = ID), +    ​SET ​ID             = (SELECT ​NewID FROM #​AccountContactMap ​WHERE OldID = ID), 
-        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,  +        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 
- CCCSCustomerGuid = NULL,  +        CCCSCustomerGuid = NULL, 
- UserID = NULL,    +        UserID = NULL, 
- UseShippingAccountInfo = 0, DefaultShippingCarrierID = NULL, ShippingMethodLinksXML = NULL,  +        UseShippingAccountInfo = 0, DefaultShippingCarrierID = NULL, ShippingMethodLinksXML = NULL, 
- AccountID ​     = (select ​NewID from #​AccountMap ​     ​where OldID = AccountID),​ +        AccountID ​     = (SELECT ​NewID FROM #​AccountMap ​     ​WHERE OldID = AccountID),​ 
- SalesPersonID1 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID1),​ +        SalesPersonID1 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID1),​ 
- SalesPersonID2 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID2),​ +        SalesPersonID2 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID2),​ 
- SalesPersonID3 = (select ​NewID from #​EmployeeMap ​    where OldID = SalesPersonID3),​ +        SalesPersonID3 = (SELECT ​NewID FROM #​EmployeeMap ​    WHERE OldID = SalesPersonID3),​ 
-        BillingAddressID ​ = (select ​NewID from #​AddressMap ​  where OldID = BillingAddressID),​ +        BillingAddressID ​ = (SELECT ​NewID FROM #​AddressMap ​  WHERE OldID = BillingAddressID),​ 
-        ShippingAddressID = (select ​NewID from #​AddressMap ​  where OldID = ShippingAddressID),​ +        ShippingAddressID = (SELECT ​NewID FROM #​AddressMap ​  WHERE OldID = ShippingAddressID),​ 
-        PaymentAddressID ​ = (select ​NewID from #​AddressMap ​  where OldID = PaymentAddressID),​ +        PaymentAddressID ​ = (SELECT ​NewID FROM #​AddressMap ​  WHERE OldID = PaymentAddressID),​ 
-        PaymentAddressLinkID ​ = (select ​NewID from #​AddressLinkMap ​  where OldID = PaymentAddressLinkID),​ +        PaymentAddressLinkID ​ = (SELECT ​NewID FROM #​AddressLinkMap ​  WHERE OldID = PaymentAddressLinkID),​ 
-        MainPhoneNumberID = (select ​NewID from #​PhoneNumberMap ​    where OldID = MainPhoneNumberID),​ +        MainPhoneNumberID = (SELECT ​NewID FROM #​PhoneNumberMap ​    WHERE OldID = MainPhoneNumberID),​ 
-        MainFaxNumberID ​  = (select ​NewID from #​PhoneNumberMap ​    where OldID = MainFaxNumberID) +        MainFaxNumberID ​  = (SELECT ​NewID FROM #​PhoneNumberMap ​    WHERE OldID = MainFaxNumberID) 
-+    
-    IF (@debug=1) SELECT TOP 10 ID, AccountID, * from #​AccountContactTemp;​ +    IF (@debug=1) SELECT TOP 10 ID, AccountID, * FROM #​AccountContactTemp;​ 
- update ​#​AddressTemp +    ​UPDATE ​#​AddressTemp 
- set ID             = (select ​NewID from #​AddressMap ​   ​where OldID = ID), +    ​SET ​ID             = (SELECT ​NewID FROM #​AddressMap ​   ​WHERE OldID = ID), 
-        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,  +        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 
- TaxClassID = NULL +        TaxClassID = NULL 
-+    
- update ​#​AddressLinkTemp +    ​UPDATE ​#​AddressLinkTemp 
- set ID             = (select ​NewID from #​AddressLinkMap ​   ​where OldID = ID), +    ​SET ​ID             = (SELECT ​NewID FROM #​AddressLinkMap ​   ​WHERE OldID = ID), 
-        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,  +        ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0, 
-        AddressID ​     = (select ​NewID from #​AddressMap ​   ​where OldID = AddressID),​ +        AddressID ​     = (SELECT ​NewID FROM #​AddressMap ​   ​WHERE OldID = AddressID),​ 
-        ParentID ​      = CASE WHEN ParentClassTypeID = 2000  +        ParentID ​      = CASE WHEN ParentClassTypeID = 2000 
-                              THEN (select ​NewID from #​AccountMap ​where OldID = ParentID) +                              THEN (SELECT ​NewID FROM #​AccountMap ​WHERE OldID = ParentID) 
-                              WHEN ParentClassTypeID = 3000  +                              WHEN ParentClassTypeID = 3000 
-                              THEN (select ​NewID from #​AccountContactMap ​where OldID = ParentID)+                              THEN (SELECT ​NewID FROM #​AccountContactMap ​WHERE OldID = ParentID)
                               ELSE ParentID                               ELSE ParentID
                           END                           END
- +    ​
- update ​#​PhoneNumberTemp +    ​UPDATE ​#​PhoneNumberTemp 
- set ID             = (select ​NewID from #​PhoneNumberMap ​   ​where OldID = ID),+    ​SET ​ID             = (SELECT ​NewID FROM #​PhoneNumberMap ​   ​WHERE OldID = ID),
         ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,         ModifiedByUser = '​Import',​ ModifiedByComputer = NULL, ModifiedDate = NULL, SeqID = 0,
-        ParentID ​      = CASE WHEN ParentClassTypeID = 2000  +        ParentID ​      = CASE WHEN ParentClassTypeID = 2000 
-                              THEN (select ​NewID from #​AccountMap ​where OldID = ParentID) +                              THEN (SELECT ​NewID FROM #​AccountMap ​WHERE OldID = ParentID) 
-                              WHEN ParentClassTypeID = 3000  +                              WHEN ParentClassTypeID = 3000 
-                              THEN (select ​NewID from #​AccountContactMap ​where OldID = ParentID)+                              THEN (SELECT ​NewID FROM #​AccountContactMap ​WHERE OldID = ParentID)
                               ELSE ParentID                               ELSE ParentID
                           END                           END
- ;+    ​;
 -- Step 6. Insert the Temp Tables -- Step 6. Insert the Temp Tables
- BEGIN TRANSACTION +    ​BEGIN TRANSACTION 
- BEGIN TRY +    BEGIN TRY 
-  + 
- -- insert the primary data +        -- insert the primary data 
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.Account SELECT * FROM #​AccountTemp';​ +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.Account SELECT * FROM #​AccountTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountContact SELECT * FROM #​AccountContactTemp';​ +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountContact SELECT * FROM #​AccountContactTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.Address SELECT * FROM #​AddressTemp';​ +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.Address SELECT * FROM #​AddressTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AddressLink SELECT * FROM #​AddressLinkTemp';​ +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AddressLink SELECT * FROM #​AddressLinkTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.PhoneNumber SELECT * FROM #​PhoneNumberTemp';​ +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.PhoneNumber SELECT * FROM #​PhoneNumberTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- -- now create the stub entries for the UDF tables +        -- now create the stub entries for the UDF tables 
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountUserField  +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountUserField 
- (ID, StoreID, ClassTypeID,​ SeqID, IsSystem, IsActive) +                    (ID, StoreID, ClassTypeID,​ SeqID, IsSystem, IsActive) 
- SELECT ID, -1, 2001, 0, 0, 1 +                    SELECT ID, -1, 2001, 0, 0, 1 
- FROM #​AccountTemp';​ +                    FROM #​AccountTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- set @sql = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountContactUserField  +        ​SET ​@SQL = '​INSERT INTO ['​+@TargetDatabase+'​].dbo.AccountContactUserField 
- (ID, StoreID, ClassTypeID,​ SeqID, IsSystem, IsActive) +                    (ID, StoreID, ClassTypeID,​ SeqID, IsSystem, IsActive) 
- SELECT ID, -1, 3001, 0, 0, 1 +                    SELECT ID, -1, 3001, 0, 0, 1 
- FROM #​AccountContactTemp';​ +                    FROM #​AccountContactTemp';​ 
- EXEC (@sql);  IF (@debug = 1) PRINT @sql+        EXEC (@SQL);  IF (@debug = 1) PRINT @SQL
- IF (@RollBackImport = 1)  +        IF (@RollBackImport = 1) 
- ROLLBACK TRANSACTION  +            ROLLBACK TRANSACTION 
- ELSE +        ELSE 
- COMMIT TRANSACTION +            COMMIT TRANSACTION 
- +        
- END TRY+    END TRY
     BEGIN CATCH     BEGIN CATCH
         ROLLBACK TRANSACTION;​         ROLLBACK TRANSACTION;​
- declare ​@ErrorMessage ​   VARCHAR(2048);​ +        DECLARE ​@ErrorMessage ​   VARCHAR(2048);​ 
- declare ​@ErrorSeverity ​  ​INT;​ +        ​DECLARE ​@ErrorSeverity ​  ​INT;​ 
- declare ​@ErrorState ​     INT;+        ​DECLARE ​@ErrorState ​     INT;
         SELECT @ErrorMessage = ERROR_MESSAGE(),​         SELECT @ErrorMessage = ERROR_MESSAGE(),​
                ​@ErrorSeverity = ERROR_SEVERITY(),​                ​@ErrorSeverity = ERROR_SEVERITY(),​
                ​@ErrorState = ERROR_STATE();​                ​@ErrorState = ERROR_STATE();​
-        -- Use RAISERROR inside the CATCH block to return  +        -- Use RAISERROR inside the CATCH block to return 
-        -- error information about the original error that +        -- error information about the original error that
         -- caused execution to jump to the CATCH block.         -- caused execution to jump to the CATCH block.
         RAISERROR (@ErrorMessage,​ -- Message text.         RAISERROR (@ErrorMessage,​ -- Message text.
Line 477: Line 449:
     END CATCH;     END CATCH;
 </​code>​ </​code>​
- 
- 
  
 ====== Source ====== ====== Source ======
- 
- 
  
 Contributor:​ Cyrious Consulting Contributor:​ Cyrious Consulting
- 
- 
  
 Date: 7/2016 Date: 7/2016
- 
- 
  
 Version: Control 5.7 Version: Control 5.7
- 
-