Get Six Digits Coupon Code (unique)


Create table test (id int, pinCode varchar(6))

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n between 1 and 100),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)


,mycte as (
Select *, row_number() Over(partition by Pincode Order by n) rn
 from (Select Left(Convert(varchar(36),NEWID()),6) as pincode ) t
cross apply (select n from nums WHERE n<10000) d(n)

)


Select * 
into myRandomCodeTable
from mycte 
where rn=1
 
 Select * from   myRandomCodeTable


--drop table   myRandomCodeTable
drop table test 
 
 
 
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s