Thread: Multiple Coupon Codes via Import

Is there a way to import many Coupon Codes at one time, via an upload tool? Or is it only possible to manually create each Coupon for use in the Cart?

There aren't any built-in bulk import tools for coupons, however their structure is pretty simple in the DB. You could just do a basic SQL query to do that.

Originally Posted by AspDotNetStorefront Staff - Scott

There aren't any built-in bulk import tools for coupons, however their structure is pretty simple in the DB. You could just do a basic SQL query to do that.

Hi Scott,
So, would we need to put anything into the CouponGUID column?

I think we need to do a bulk coupon insert as we want to offer around $100 coupons that can only be used once but not via the option of once per customer as it would be easy enough for someone to re-sign up.

So was thinking of using a random string generator to give us a few hundred codes and insert them direct into the table for sending out to specific clients.

Any help would be appreciated.

Nope you don't. Sql will auto generate it once a new record is inserted based on (newid()) binding.

Usual provisos, make a backup of the database first and try it out on a development database

What you want is something like this:

Code:

INSERT INTO [AspDotNetStorefront].[dbo].[Coupon]
           ([CouponGUID]
           ,[CouponCode]
           ,[Description]
           ,[ExpirationDate]
           ,[DiscountPercent]
           ,[DiscountAmount]
           ,[DiscountIncludesFreeShipping]
           ,[ExpiresOnFirstUseByAnyCustomer]
           ,[ExpiresAfterOneUsageByEachCustomer]
           ,[ExpiresAfterNUses]
           ,[RequiresMinimumOrderAmount]
           ,[ValidForCustomers]
           ,[ValidForProducts]
           ,[ValidForManufacturers]
           ,[ValidForCategories]
           ,[ValidForSections]
           ,[CouponType]
           ,[NumUses]
           ,[ExtensionData]
           ,[Deleted]
           ,[CreatedOn])
SELECT newid(),
	'COUP' + convert(varchar(10),customerid),
	'coupon for customer ' + convert(varchar(10),customerid),
	'31-Dec-2010 23:59:59.999',
	0,
	100,
	1,
	1,
	1,
	1,
	0,
	convert(varchar(10),customerid),
	null,
	null,
	null,
	null,
	0,
	0,
	null,
	0,
	getdate()
from customer
where isregistered=1

This will insert a coupon named after the customer's unique id for all registered customers.

Alter as you see fit!

Originally Posted by AspDotNetStorefront Staff - Scott

There aren't any built-in bulk import tools for coupons, however their structure is pretty simple in the DB. You could just do a basic SQL query to do that.

Is there a dummy proof way of doing this? I have no idea where to begin and I need to upload 500 coupon codes. Any help would greatly be appreciated.

stevenwong, do you have access to SQL Management Studio? As always, do a backup of SQL plus if possible test on a dev db.

The way I did my testing and worked from memory is, first a created a coupon like what i needed from the ADNSF admin area.
Then in SQL Management Studio, I opened the Coupon table to view and edit results.
I checked the coupon I created to see what details were there.

This ain't pretty, but I'm sure i just did a copy of my coupon codes from my notepad file and pasted the column into the next empty row, under CouponCode. After that I copied the other fields except CouponGUID down to fill the rest of the columns.

Checked admin area and all were there.

This was a while ago though.