Subject: | NOP Commerce - Extract Customer Information | Summary: | The NOP Commerce Database holds customer contacts in three tables:
1. nop_customer 2. nop_customerAttribute - This holds primary contact information 3. nop_address - Billing and Shipping information
The script below extracts this information from the NOP Database into a tabular format.
declare @ReportData table ( CustomerID int, Email varchar(100), UserName varchar(100), RegistrationDate varchar(100), FirstName varchar(100), LastName varchar(100), Company varchar(100), StreetAddress varchar(100), StreetAddress2 varchar(100), ZipPostalCode varchar(100), City varchar(100), PhoneNumber varchar(100) )
Insert into @ReportData (CustomerID, Email, UserName) Select CustomerID, Email, UserName from nop_customer
Update @ReportData set Firstname = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'FirstName'
Update @ReportData set LastName = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'LastName'
Update @ReportData set Company = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'Company' Update @ReportData set StreetAddress = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'StreetAddress'
Update @ReportData set StreetAddress2 = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'StreetAddress2'
Update @ReportData set ZipPostalCode = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'ZipPostalCode'
Update @ReportData set City = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'City'
Update @ReportData set PhoneNumber = b.Value from @ReportData a inner join nop_customerAttribute b on a.CustomerID = b.CustomerID and b.[key] = 'PhoneNumber' select * from @ReportData
| Audit Notes: | |
|