How to make a SQL table bigger

I have a table with 100 rows with double as data

a1 a2 a3...
---------
1  2   3
23 55  4
2  3   7

I am planning to use UNION ALL to make that table bigger

a1 a2 a3...
---------
1  2   3
23 55  4
2  3   7
1  2   3
23 55  4
2  3   7
1  2   3
23 55  4
2  3   7

This is for testing purposes so what do you recommend, what woul开发者_如何学Pythond be the most efficient way to do this?

This will increase the size of your table exponentially... First it inserts x records, then 2x, then 4x, then 8x... You could add distinct or top n, etc. to the select if you just want to add the same number of records each time.

DECLARE @count int
DECLARE @max int

SET @count int = 1
SET @max = 10

WHILE @count < @max
BEGIN
   INSERT INTO myTable (a1, a2, a3)
   SELECT a1, a2, a3 FROM myTable
   SET @count = @count + 1
END

BTW -- not sure what you're trying to test, but you might add something besides whole numbers to your data set -- e.g., 1.01, .99, 55.7, 60, etc.

EDIT

Per your comment -- if you really want to use union all then...

INSERT INTO myTABLE (a1, a2, a3)
SELECT a1, a2, a3 FROM
(
SELECT a1, a2, a3 FROM myTable
UNION ALL
SELECT a1, a2, a3 FROM myTable
UNION ALL
SELECT a1, a2, a3 FROM myTable
...
) a

INSERT dbo.Table
  SELECT /* TOP (n) */ t1.a1, t1.a2, t1.a3
  FROM       dbo.Table AS t1
  CROSS JOIN dbo.Table AS t2
-- repeat CROSS JOINs as necessary 

The first cross join will square, second cross join will cube, etc. Luke @kuru's answer you can limit the number of rows added using TOP in case you don't want to do the math.