Differences
This shows you the differences between two versions of the page.
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 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, | ||
- | |||
- | |||
====== 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__ | ||
- | |||
- | 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__ | ||
- | |||
- | 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 | + | |
- | DECLARE @TargetDatabase | + | DECLARE @TargetDatabase |
- | -- 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 | + | |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | create table #AIDs (ID int primary key); | + | |
- | insert into #AIDs | + | |
- | select | + | |
- | where | + | |
- | and IsActive = 1 | + | |
- | | + | -- and have orders within the last 5 years |
- | and ID in ( | + | |
- | | + | |
- | | + | |
- | | + | |
- | | + | |
- | + | ||
) | ) | ||
- | order by id | + | ORDER BY id |
- | ; | + | |
- | -- table of account contact IDs | + | -- table of account contact IDs |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | create table #ACIDs (ID int primary key); | + | |
- | insert into #ACIDs | + | |
- | select | + | |
-- Step 2, do some clean up | -- Step 2, do some clean up | ||
- | IF OBJECT_ID(' | + | |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
- | IF OBJECT_ID(' | + | IF OBJECT_ID(' |
-- Step 3, create temp tables with the data | -- Step 3, create temp tables with the data | ||
- | -- Account Table | + | |
- | SELECT A.* | + | SELECT A.* |
- | INTO # | + | INTO # |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- AccountContact Table | + | -- AccountContact Table |
- | SELECT AC.* | + | SELECT AC.* |
- | INTO # | + | INTO # |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Address Link Table | + | -- Address Link Table |
- | SELECT ADL.* | + | SELECT ADL.* |
- | INTO # | + | INTO # |
- | FROM AddressLink ADL | + | FROM AddressLink ADL |
- | LEFT JOIN #AIDs A | + | LEFT JOIN #AIDs A |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Address Table | + | -- Address Table |
- | SELECT DISTINCT AD.* | + | SELECT DISTINCT AD.* |
- | INTO # | + | INTO # |
- | FROM Address AD | + | FROM Address AD |
- | JOIN # | + | JOIN # |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Phone Number Table | + | -- Phone Number Table |
- | SELECT P.* | + | SELECT P.* |
- | INTO # | + | INTO # |
- | FROM PhoneNumber P | + | FROM PhoneNumber P |
- | LEFT JOIN #AIDs A | + | LEFT JOIN #AIDs A |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Promotion Table | + | -- Promotion Table |
- | SELECT PR.* | + | SELECT PR.* |
- | INTO # | + | INTO # |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Pricing Level Table | + | -- Pricing Level Table |
- | SELECT PL.* | + | SELECT PL.* |
- | INTO # | + | INTO # |
- | 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 # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
-- Step 4 - Create Map Tables for Fields not Copied | -- Step 4 - Create Map Tables for Fields not Copied | ||
- | -- Create Employee Map | + | |
- | SELECT DISTINCT T.OldID, FirstName, LastName, | + | SELECT DISTINCT T.OldID, FirstName, LastName, |
- | INTO # | + | INTO # |
- | FROM | + | FROM |
- | ( | + | ( |
- | select | + | |
- | union | + | UNION |
- | select | + | |
- | union | + | UNION |
- | select | + | |
- | union | + | UNION |
- | select | + | |
- | union | + | UNION |
- | select | + | |
- | union | + | UNION |
- | select | + | |
- | ) T | + | ) T |
- | join Employee E on E.ID = T.OldID | + | |
- | ; | + | ; |
- | SET @sql = ' | + | SET @SQL = ' |
- | Set NewID = E2.ID | + | Set NewID = E2.ID |
- | From # | + | From # |
- | Join [' | + | Join [' |
- | | + | on EM.LastName = E2.LastName and EM.FirstName = E2.FirstName' |
- | ; | + | ; |
- | exec (@sql) | + | |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Create Payment Terms Map | + | -- Create Payment Terms Map |
- | SELECT DISTINCT PT.ID as OldID, PT.TermsName | + | SELECT DISTINCT PT.ID AS OldID, PT.TermsName |
- | INTO # | + | INTO # |
- | FROM Account A | + | FROM Account A |
- | join #AIDs I on A.ID = I.ID | + | |
- | join PaymentTerms PT on PT.ID = A.PaymentTermsID | + | |
- | ; | + | ; |
- | SET @sql = ' | + | SET @SQL = ' |
- | Set NewID = PT2.ID | + | Set NewID = PT2.ID |
- | From # | + | From # |
- | Join [' | + | Join [' |
- | | + | on PTM.TermsName = PT2.TermsName' |
- | ; | + | ; |
- | exec (@sql) | + | |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | -- Create Tax Class Map | + | -- Create Tax Class Map |
- | SELECT DISTINCT TC.ID as OldID, TC.TaxClassName | + | SELECT DISTINCT TC.ID AS OldID, TC.TaxClassName |
- | INTO # | + | INTO # |
- | FROM Account A | + | FROM Account A |
- | join #AIDs I on A.ID = I.ID | + | |
- | join TaxClass TC on TC.ID = A.TaxClassID | + | |
- | ; | + | ; |
- | SET @sql = ' | + | SET @SQL = ' |
- | Set NewID = TC2.ID | + | Set NewID = TC2.ID |
- | From # | + | From # |
- | Join [' | + | Join [' |
- | | + | on TCM.TaxClassName = TC2.TaxClassName' |
- | ; | + | ; |
- | exec (@sql) | + | |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | ; | + | ; |
- | + | ||
- | -- Create Indusry Map | + | -- Create Indusry Map |
- | SELECT DISTINCT M.ID as OldID, M.ItemName, | + | SELECT DISTINCT M.ID AS OldID, M.ItemName, |
- | INTO # | + | INTO # |
- | FROM MarketingListItem M | + | FROM MarketingListItem M |
- | join # | + | |
- | WHERE M.MarketingListID = 10 | + | WHERE M.MarketingListID = 10 |
- | ; | + | ; |
- | SET @sql = ' | + | SET @SQL = ' |
- | Set NewID = TC2.ID | + | Set NewID = TC2.ID |
- | From # | + | From # |
- | Join [' | + | Join [' |
- | | + | on IM.ItemName = TC2.ItemName |
Where TC2.MarketingListID = 10' | Where TC2.MarketingListID = 10' | ||
- | ; | + | |
- | exec (@sql) | + | |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | ; | + | ; |
- | + | ||
- | -- Create Origin Map | + | -- Create Origin Map |
- | SELECT DISTINCT M.ID as OldID, M.ItemName, | + | SELECT DISTINCT M.ID AS OldID, M.ItemName, |
- | INTO # | + | INTO # |
- | FROM MarketingListItem M | + | FROM MarketingListItem M |
- | join # | + | |
- | WHERE M.MarketingListID = 11 | + | WHERE M.MarketingListID = 11 |
- | ; | + | ; |
- | SET @sql = ' | + | SET @SQL = ' |
- | Set NewID = TC2.ID | + | Set NewID = TC2.ID |
- | From #OriginMap OM | + | From #OriginMap OM |
- | Join [' | + | Join [' |
- | | + | on OM.ItemName = TC2.ItemName |
Where TC2.MarketingListID = 11' | Where TC2.MarketingListID = 11' | ||
- | ; | + | |
- | exec (@sql) | + | |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 5 * FROM # | + | IF (@debug=1) SELECT TOP 5 * FROM # |
- | ; | + | ; |
- | -- 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 = ' | + | |
- | SET @ParamDefinition = ' | + | SET @ParamDefinition = ' |
- | EXEC sp_executesql @SQL, @ParamDefinition, | + | EXEC sp_executesql @SQL, @ParamDefinition, |
IF (COALESCE(@MaxCurrentID, | IF (COALESCE(@MaxCurrentID, | ||
- | + | ||
- | 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 # | FROM # | ||
ORDER BY CompanyName | ORDER BY CompanyName | ||
- | -- 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 = ' | + | |
- | SET @ParamDefinition = ' | + | SET @ParamDefinition = ' |
- | EXEC sp_executesql @SQL, @ParamDefinition, | + | EXEC sp_executesql @SQL, @ParamDefinition, |
IF (COALESCE(@MaxCurrentID, | IF (COALESCE(@MaxCurrentID, | ||
- | + | ||
- | 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 # | INTO # | ||
FROM # | FROM # | ||
- | -- 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 = ' | + | |
- | SET @ParamDefinition = ' | + | SET @ParamDefinition = ' |
- | EXEC sp_executesql @SQL, @ParamDefinition, | + | EXEC sp_executesql @SQL, @ParamDefinition, |
IF (COALESCE(@MaxCurrentID, | IF (COALESCE(@MaxCurrentID, | ||
- | + | ||
- | 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 # | FROM # | ||
- | -- 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 = ' | + | |
- | SET @ParamDefinition = ' | + | SET @ParamDefinition = ' |
- | EXEC sp_executesql @SQL, @ParamDefinition, | + | EXEC sp_executesql @SQL, @ParamDefinition, |
IF (COALESCE(@MaxCurrentID, | IF (COALESCE(@MaxCurrentID, | ||
- | + | ||
- | 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 # | INTO # | ||
FROM # | FROM # | ||
- | -- 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 = ' | + | |
- | SET @ParamDefinition = ' | + | SET @ParamDefinition = ' |
- | EXEC sp_executesql @SQL, @ParamDefinition, | + | EXEC sp_executesql @SQL, @ParamDefinition, |
IF (COALESCE(@MaxCurrentID, | IF (COALESCE(@MaxCurrentID, | ||
- | + | ||
- | 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 # | INTO # | ||
- | FROM # | + | FROM # |
-- 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, | + | IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID, |
- | update | + | |
- | set ID = (select | + | |
ModifiedByUser = ' | ModifiedByUser = ' | ||
- | PrimaryContactID | + | |
- | AccountingContactID = (select | + | AccountingContactID = (SELECT |
- | SalesPersonID1 = (select | + | SalesPersonID1 = (SELECT |
- | SalesPersonID2 = (select | + | SalesPersonID2 = (SELECT |
- | SalesPersonID3 = (select | + | SalesPersonID3 = (SELECT |
- | PaymentTermsID = (select | + | PaymentTermsID = (SELECT |
- | TaxClassID | + | TaxClassID |
- | IndustryID | + | IndustryID |
- | OriginID | + | OriginID |
- | BillingAddressID | + | BillingAddressID |
- | ShippingAddressID = (select | + | ShippingAddressID = (SELECT |
- | MainPhoneNumberID = (select | + | MainPhoneNumberID = (SELECT |
- | MainFaxNumberID | + | MainFaxNumberID |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID, | + | IF (@debug=1) SELECT TOP 10 ID, PrimaryContactID, |
- | IF (@debug=1) SELECT TOP 10 ID, AccountID, * from # | + | IF (@debug=1) SELECT TOP 10 ID, AccountID, * FROM # |
- | update | + | |
- | set ID = (select | + | |
- | ModifiedByUser = ' | + | ModifiedByUser = ' |
- | CCCSCustomerGuid = NULL, | + | CCCSCustomerGuid = NULL, |
- | UserID = NULL, | + | UserID = NULL, |
- | UseShippingAccountInfo = 0, DefaultShippingCarrierID = NULL, ShippingMethodLinksXML = NULL, | + | UseShippingAccountInfo = 0, DefaultShippingCarrierID = NULL, ShippingMethodLinksXML = NULL, |
- | AccountID | + | AccountID |
- | SalesPersonID1 = (select | + | SalesPersonID1 = (SELECT |
- | SalesPersonID2 = (select | + | SalesPersonID2 = (SELECT |
- | SalesPersonID3 = (select | + | SalesPersonID3 = (SELECT |
- | BillingAddressID | + | BillingAddressID |
- | ShippingAddressID = (select | + | ShippingAddressID = (SELECT |
- | PaymentAddressID | + | PaymentAddressID |
- | PaymentAddressLinkID | + | PaymentAddressLinkID |
- | MainPhoneNumberID = (select | + | MainPhoneNumberID = (SELECT |
- | MainFaxNumberID | + | MainFaxNumberID |
- | ; | + | ; |
- | IF (@debug=1) SELECT TOP 10 ID, AccountID, * from # | + | IF (@debug=1) SELECT TOP 10 ID, AccountID, * FROM # |
- | update | + | |
- | set ID = (select | + | |
- | ModifiedByUser = ' | + | ModifiedByUser = ' |
- | TaxClassID = NULL | + | TaxClassID = NULL |
- | ; | + | ; |
- | update | + | |
- | set ID = (select | + | |
- | ModifiedByUser = ' | + | ModifiedByUser = ' |
- | AddressID | + | AddressID |
- | ParentID | + | ParentID |
- | THEN (select | + | THEN (SELECT |
- | WHEN ParentClassTypeID = 3000 | + | WHEN ParentClassTypeID = 3000 |
- | THEN (select | + | THEN (SELECT |
ELSE ParentID | ELSE ParentID | ||
END | END | ||
- | ; | + | |
- | update | + | |
- | set ID = (select | + | |
ModifiedByUser = ' | ModifiedByUser = ' | ||
- | ParentID | + | ParentID |
- | THEN (select | + | THEN (SELECT |
- | WHEN ParentClassTypeID = 3000 | + | WHEN ParentClassTypeID = 3000 |
- | THEN (select | + | THEN (SELECT |
ELSE ParentID | ELSE ParentID | ||
END | END | ||
- | ; | + | |
-- Step 6. Insert the Temp Tables | -- Step 6. Insert the Temp Tables | ||
- | BEGIN TRANSACTION | + | |
- | BEGIN TRY | + | BEGIN TRY |
- | + | ||
- | -- insert the primary data | + | -- insert the primary data |
- | set @sql = ' | + | |
- | EXEC (@sql); IF (@debug = 1) PRINT @sql; | + | EXEC (@SQL); IF (@debug = 1) PRINT @SQL; |
- | set @sql = ' | + | |
- | EXEC (@sql); IF (@debug = 1) PRINT @sql; | + | EXEC (@SQL); IF (@debug = 1) PRINT @SQL; |
- | set @sql = ' | + | |
- | EXEC (@sql); IF (@debug = 1) PRINT @sql; | + | EXEC (@SQL); IF (@debug = 1) PRINT @SQL; |
- | set @sql = ' | + | |
- | EXEC (@sql); IF (@debug = 1) PRINT @sql; | + | EXEC (@SQL); IF (@debug = 1) PRINT @SQL; |
- | set @sql = ' | + | |
- | 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 = ' | + | |
- | (ID, StoreID, ClassTypeID, | + | (ID, StoreID, ClassTypeID, |
- | SELECT ID, -1, 2001, 0, 0, 1 | + | SELECT ID, -1, 2001, 0, 0, 1 |
- | FROM # | + | FROM # |
- | EXEC (@sql); IF (@debug = 1) PRINT @sql; | + | EXEC (@SQL); IF (@debug = 1) PRINT @SQL; |
- | set @sql = ' | + | |
- | (ID, StoreID, ClassTypeID, | + | (ID, StoreID, ClassTypeID, |
- | SELECT ID, -1, 3001, 0, 0, 1 | + | SELECT ID, -1, 3001, 0, 0, 1 |
- | FROM # | + | FROM # |
- | 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 | + | DECLARE |
- | declare | + | |
- | declare | + | |
SELECT @ErrorMessage = ERROR_MESSAGE(), | SELECT @ErrorMessage = ERROR_MESSAGE(), | ||
| | ||
| | ||
- | -- 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, | RAISERROR (@ErrorMessage, | ||
Line 477: | Line 449: | ||
END CATCH; | END CATCH; | ||
</ | </ | ||
- | |||
- | |||
====== Source ====== | ====== Source ====== | ||
- | |||
- | |||
Contributor: | Contributor: | ||
- | |||
- | |||
Date: 7/2016 | Date: 7/2016 | ||
- | |||
- | |||
Version: Control 5.7 | Version: Control 5.7 | ||
- | |||
- | |||