Duplicate SQL table to creat large dummy table

Question

I have an SQL table already, the format of which I just want duplicating for testing purposes. I tried the following:

DECLARE @Counter AS INT(1)
WHILE ( @Counter <= 10)
BEGIN 
CREATE TABLE my_new_big_table
  AS (SELECT * FROM my_table)
  SET @Counter = @Counter +1
END;

However I encounter an error with 'DECLARE'

ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'DECLARE'(line 1, pos 0)

Is this the best approach to duplicate an existing table?

Answer 1

Your approach seems wrong. With a CREATE TABLE statement in the loop, you'd try to create the same table ten times.

Here is a solution in pure SQL that I hope will work for you. I take the original table and cross join it with 10 generated rows, so as to get the original rows tenfold.

CREATE TABLE my_new_big_table AS
  SELECT t.*
  FROM my_table t
  CROSS JOIN VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) v(i);