Select 0 AS A, 1 AS B FROM someTable
Based on the above query, I can hardcode the number of column and the data regardless of what data is in someTable, and the number of rows is depending on the number of row in someTable. I'm wondering what can I do if I want to hardcode the number of row as well?
For example if someTable have only 10 rows, how should I modify the above query so that I can have 1000 lines of records?
You can just keep cross joining your table:
SELECT TOP 1000 0 AS A, 1 AS B
FROM someTable a
CROSS JOIN someTable b
CROSS JOIN someTable c
CROSS JOIN someTable d;
I am assuming from the fact that you have tagged with SSMS this is SQL Server, If not you may need to use LIMIT
SELECT 0 AS A, 1 AS B
FROM someTable a
CROSS JOIN someTable b
CROSS JOIN someTable c
CROSS JOIN someTable d
LIMIT 1000;
The problem here is that if SomeTable
only has 1 row, it won't matter how many times you cross join it, you will still only have one row. If you don't actually care about the values in the table, and only want to use it to generate rows then you could just use a system view that you know has more rows than you need (again assuming SQL Server):
SELECT TOP 1000 0 AS A, 1 AS B
FROM sys.all_objects a;
Even on an empty database sys.all_objects
will have 2083 rows. If you might need more then just CROSS JOIN
the views:
SELECT TOP 1000 0 AS A, 1 AS B
FROM sys.all_objects a
CROSS JOIN sys.all_objects b;
This will give you 4,338,889 rows.