0

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?

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
pymat
  • 1,090
  • 1
  • 23
  • 45

1 Answers1

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);
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • thank you for that. I'll need to loop also for 100 and 1000, so this would not work for larger iterations. But for 10 it seems fine. – pymat Feb 17 '23 at 07:57
  • 1
    For larger factors you can use [SEQUENCE](https://docs.databricks.com/sql/language-manual/functions/sequence.html) instead of `VALUES`. You may have to combine this with [EXPLODE](https://docs.databricks.com/sql/language-manual/functions/explode.html) to get from the array that `SEQUENCE` produces to rows. I don't know. – Thorsten Kettner Feb 17 '23 at 08:15
  • btw how do you execute the same statement without including the column "i"? A simple 'drop column' does not work. – pymat Feb 17 '23 at 09:06
  • We are not selecting column i, because we dont't `select *`, but only `t.*`. Maybe you made a mistake when copying my query. Oh, dammit, I forgot to set that alias name. I've updated my answer. – Thorsten Kettner Feb 17 '23 at 09:25
  • Ketnner: I mean it works, I just have an additional column "i", which is denoted in v(i) in your suggestion. – pymat Feb 17 '23 at 09:26
  • Please see that I have an alias `t` for your original table. I select `t.*`. Thus I don't select the column `i` from the ad-hoc table `v`, which I would have got, if I just selected `*`. – Thorsten Kettner Feb 17 '23 at 10:04
  • Ok I see now. Regarding the iteration for 100 or 1000 (SEQUENCE AND EXPLODE) I'll create a new post. Thanks again. – pymat Feb 17 '23 at 10:11