Need help to build a sql query

Question

I have a table with following schema

Sample table

email        |  name  |  address 
1@gmail.com  |  A     |  CA

I want the same set of record with five different static email addresses

Expected output

email              |   name   |   address
static1@gmail.com  |   A      |   CA
static2@gmail.com  |   A      |   CA
static3@gmail.com  |   A      |   CA
static4@gmail.com  |   A      |   CA
static5@gmail.com  |   A      |   CA

Is this possible?

Preferably Mysql ... as i am working on an internal tool which is quite similar to it — Saurabh Saxena, Apr 07 '13 at 08:20
Yes, it is possible! Take a look at this answer: [Generating a range of numbers in MySQL](http://stackoverflow.com/q/186756/1765658) — F. Hauri, Apr 07 '13 at 08:49

score 1 · Accepted Answer · answered Apr 07 '13 at 10:47

Try

SELECT CONCAT('static', @n := @n + 1, '@', SUBSTRING_INDEX(email, '@', -1)) email,
       `name`,
       address
  FROM table1 CROSS JOIN  
       INFORMATION_SCHEMA.COLUMNS JOIN
       (SELECT @n := 0) n
 LIMIT 5

Output:

|             EMAIL | NAME | ADDRESS |
--------------------------------------
| static1@gmail.com |    A |      CA |
| static2@gmail.com |    A |      CA |
| static3@gmail.com |    A |      CA |
| static4@gmail.com |    A |      CA |
| static5@gmail.com |    A |      CA |

SQLFiddle

yeah this does it, but it sounds like you just gave a spammer a solution to generate a bunch of emails. — DRapp, Apr 07 '13 at 12:00

Need help to build a sql query

1 Answers1