syntax error create table union all
Hello. I am trying to create a new table as the union of two existing tables via an R DBI connection, but am receiving an error message that I cannot seem to fix. Any advice to help correct the syntax would be greatly appreciated.
dbGetQuery(mdb,
+ "CREATE TABLE nis_0411 AS + Select * FROM nis_0407 + UNION ALL + Select * FROM is_0811")
Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE nis_0411 AS Select * FROM nis_0407 UNION ALL Select * FROM nis_0811'. Server says 'syntax error, unexpected SCOLON, expecting WITH in: "create table nis_0411 as select * from nis_0407 union all select * from nis_" ' [#42000].
Cheers
Charles
Charles DiMaggio, PhD, MPH Director of Injury Research Department of Surgery New York University School of Medicine 462 First Avenue, NBV 15 New York, NY 10016-9196 Charles.Dimaggio@nyumc.org Direct Line: 516.308.6426 (preferred) Office: 212.263.3202
Hey Charles,
When creating a table from a query, you have to specify whether you want to copy the data from the query (WITH DATA), or create an empty table, i.e. just copy the schema from the query (WITHOUT DATA). The error messages specifies that it did not expect a semicolon (i.e. "end of query"), but expected either WITH DATA or WITHOUT DATA.
Regards, Mark
On Sat, Nov 28, 2015 at 6:38 PM, Charles DiMaggio < charles.dimaggio@gmail.com> wrote:
Hello. I am trying to create a new table as the union of two existing tables via an R DBI connection, but am receiving an error message that I cannot seem to fix. Any advice to help correct the syntax would be greatly appreciated.
dbGetQuery(mdb,
- "CREATE TABLE nis_0411 AS
- Select * FROM nis_0407
- UNION ALL
- Select * FROM is_0811")
Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE nis_0411 AS Select * FROM nis_0407 UNION ALL Select * FROM nis_0811'. Server says 'syntax error, unexpected SCOLON, expecting WITH in: "create table nis_0411 as select * from nis_0407 union all select * from nis_" ' [#42000].
Cheers
Charles
Charles DiMaggio, PhD, MPH Director of Injury Research Department of Surgery New York University School of Medicine 462 First Avenue, NBV 15 New York, NY 10016-9196 Charles.Dimaggio@nyumc.org Direct Line: 516.308.6426 (preferred) Office: 212.263.3202
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Perfect. Thanks very much, Mark.
Charles
Charles DiMaggio, PhD, MPH Director of Injury Research Department of Surgery New York University School of Medicine 462 First Avenue, NBV 15 New York, NY 10016-9196 Charles.Dimaggio@nyumc.org Direct Line: 516.308.6426 (preferred) Office: 212.263.3202
On Nov 28, 2015, at 12:41 PM, Mark Raasveldt mark.raasveldt@gmail.com wrote:
Hey Charles,
When creating a table from a query, you have to specify whether you want to copy the data from the query (WITH DATA), or create an empty table, i.e. just copy the schema from the query (WITHOUT DATA). The error messages specifies that it did not expect a semicolon (i.e. "end of query"), but expected either WITH DATA or WITHOUT DATA.
Regards, Mark
On Sat, Nov 28, 2015 at 6:38 PM, Charles DiMaggio <charles.dimaggio@gmail.com mailto:charles.dimaggio@gmail.com> wrote: Hello. I am trying to create a new table as the union of two existing tables via an R DBI connection, but am receiving an error message that I cannot seem to fix. Any advice to help correct the syntax would be greatly appreciated.
dbGetQuery(mdb,
- "CREATE TABLE nis_0411 AS
- Select * FROM nis_0407
- UNION ALL
- Select * FROM is_0811")
Error in .local(conn, statement, ...) : Unable to execute statement 'CREATE TABLE nis_0411 AS Select * FROM nis_0407 UNION ALL Select * FROM nis_0811'. Server says 'syntax error, unexpected SCOLON, expecting WITH in: "create table nis_0411 as select * from nis_0407 union all select * from nis_" ' [#42000].
Cheers
Charles
Charles DiMaggio, PhD, MPH Director of Injury Research Department of Surgery New York University School of Medicine 462 First Avenue, NBV 15 New York, NY 10016-9196 Charles.Dimaggio@nyumc.org mailto:Charles.Dimaggio@nyumc.org Direct Line: 516.308.6426 tel:516.308.6426 (preferred) Office: 212.263.3202 tel:212.263.3202
users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list https://www.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Charles DiMaggio
-
Mark Raasveldt