CODEWITHSUNDEEP
mysqlsql
Thomas
Thomas

Reputation: 6690

Generating "Fake" Records Within A Query

I have a very basic statement, e.g.: 

SELECT pet, animal_type, number_of_legs 
FROM table

However, where table currently is, I want to insert some fake data, along the lines of:

rufus       cat     3
franklin    turtle  1
norm        dog     5

Is it possible to "generate" these fake records, associating each value with the corresponding field, from within a query so that they are returned as the result of the query?

Upvotes: 11

Views: 8842

Answers (3)

Kashyap
Kashyap

Reputation: 17476

If anyone is looking for Spark SQL, then syntax requires a few extra braces:

SELECT 'johny' AS pet, 'human' AS animal_type, 2 AS number_of_legs 
UNION (SELECT 'rufus',    'cat',    3)
UNION (SELECT 'franklin', 'turtle', 1)
UNION (SELECT 'norm',     'dog',    5)

OR

SELECT pet, animal_type, number_of_legs FROM existing_table
UNION (SELECT 'rufus',    'cat',    3)
UNION (SELECT 'franklin', 'turtle', 1)
UNION (SELECT 'norm',     'dog',    5)

Upvotes: 0

Saic Siquot
Saic Siquot

Reputation: 6513

SELECT pet, animal_type, number_of_legs FROM table
union select 'rufus',    'cat',    3
union select 'franklin', 'turtle', 1
union select 'norm',     'dog',    5

This gives you the content of table plus the 3 records you want, avoiding duplicates, if duplicates are OK, then replace union with union all

edit: per your comment, for tsql, you can do:

select top 110 'franklin', 'turtle', 1
from sysobjects a, sysobjects b          -- this cross join gives n^2 records

Be sure to chose a table where n^2 is greater than the needed records or cross join again and again

Upvotes: 11

Tim Lewis
Tim Lewis

Reputation: 29288

I'm not entirely sure what you're trying to do, but MySQL is perfectly capable of selecting "mock" data and printing it in a table:

SELECT "Rufus" AS "Name", "Cat" as "Animal", "3" as "Number of Legs" 
UNION 
SELECT "Franklin", "Turtle", "1" 
UNION 
SELECT "Norm", "Dog", "5";

Which would result in:

+----------+--------+----------------+
| Name     | Animal | Number of Legs |
+----------+--------+----------------+
| Rufus    | Cat    | 3              |
| Franklin | Turtle | 1              |
| Norm     | Dog    | 5              |
+----------+--------+----------------+

Doing this query this way prevents actually having to save information in a temporary table, but I'm not sure if it's the correct way of doing things.

Upvotes: 8

Related Questions