If you've ever needed to get a quick export of everyone who has purchased from your store in the last year this SQL script can be used to generate a comma separated text file. Make sure you change the "> date" part to be the correct starting point for your export. You'll get customers' names, address, phone and email as long as they placed an order on your store.
SELECT 'LastName, FirstName, Line1, Line2, City,
RegionName, PostalCode, CountryName,
Phone, Email' AS Expr1, 0 AS [Order]
UNION
SELECT CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/LastName/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/FirstName/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/Line1/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/tLine2/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/City/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/RegionName/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/PostalCode/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/CountryName/text()') AS nvarchar(1000))
+ ', ' + CAST(CAST(AddressBook AS xml ).query(N'/AddressBook/Address[last()]/Phone/text()') AS nvarchar(1000))
+ ', ' + Email AS Expr1, 1 AS [Order]
FROM bvc_User
WHERE (bvin IN
(SELECT DISTINCT UserId
FROM bvc_Order
WHERE (OrderNumber <> '') AND (TimeOfOrder > '01/01/2007')))
ORDER BY [Order]
Run this in SQL Management Studio and then copy and paste the results to a text file. You can open the text file in Excel as "comma separated" (CSV) to get sort the data or mail merge.
Be the first to rate this post
- Currently 0/5 Stars.
- 1
- 2
- 3
- 4
- 5