CODEWITHSUNDEEP
sqlamazon-redshift
Willie D
Willie D

Reputation: 145

Create multiple tables at once

Running multiple CREATE TABLE statements in redshift online query editor each ending with ';' is resulting only one table being created.

I looked at various posts on stack overflow where multiple tables were created on other platforms. Such as mysql here: SQL Create multiple tables at once. However, I have not been able to find an example for redshift that works. I also tried to remove the last ';' which resulted in only the first table being created.

CREATE TABLE IF NOT EXISTS test_one (
  test_col varchar(30),
  test_two varchar(30)
);

CREATE TABLE IF NOT EXISTS test_two (
  test_col varchar(30),
  test_two varchar(30)
);

There are no error codes, but only test_two is created.

Upvotes: 0

Views: 1363

Answers (3)

Vithal
Vithal

Reputation: 111

Redshift online query editor does not support multiple queries. You have to select individual query to execute. Better use psql or tools like Aginity for Redshift to execute multiple queries.

Upvotes: 1

Shailesh
Shailesh

Reputation: 2276

This certainly works on Redshift. If you're using Python and Psycopg2 to execute these queries, ensure there is a semi-colon ; after each query, and that there are no wild semi-colons, because you'll get as it'll be an empty query.

Also, see to it that you commit the connection after you're executing the queries. If you're using an SQL Client like DBeaver/PgAdmin, turn on auto-commit or manually commit after executing these queries.

Edit

When you're using the Redshift console query editor, it only executes one query (the last query) at a time, whether those are DDL or DML queries. So as of now it's not possible to CREATE multiple tables or INSERT data to multiple tables. You can use a an SQL Client to achieve this.

Upvotes: 1

Red Boy
Red Boy

Reputation: 5739

You have not mentioned what tool you are using, hence assuming psql command prompt.

In my.sql, I have just copied and pasted the question sql. If you run following query, it will create two example table fine without any issues.

psql -h example-url.redshift.amazonaws.com -U user -d example_db -p 5439 -f /home/your/path/to/sql/file/my.sql

Output

CREATE TABLE
CREATE TABLE

Hope it helps, please update question if you need more information.

Upvotes: 0

Related Questions