Trying to duplicate a table without data

Question

I have a table which I would like to duplicate without any data in it (Only the columns). So I have 2 types of databases, Oracle and Sql Server.

I have constructed this:

CREATE TABLE TMPTABLE AS SELECT * FROM USERS WHERE 1 = 0

It works fine in the Oracle database, but in the Sql Server I get this error:

Error Number:156,State:1,Class:15 Incorrect syntax near the keyword 'SELECT'.

Sql Server version: 14.0.1000.169.

That's not a MySQL version number. The current version of MySQL is 8.x, there's no MySQL 14. — Barmar, Sep 03 '19 at 16:06
In most databases you can use `CREATE TABLE newTable LIKE oldTable;` — Barmar, Sep 03 '19 at 16:07
Have you read the documentation whether MySQL can do `CREATE TABLE ... SELECT` or not? — Eric, Sep 03 '19 at 16:08
@Eric MySQL definitely can do that, I've used it many times. But he's apparently not using MySQL -- that's not a MySQL error message or version number. — Barmar, Sep 03 '19 at 16:08
The given query [works in MySQL](https://www.db-fiddle.com/f/gpwh9Cn9TZmCGuMeqHhoQZ/0) — Paul Spiegel, Sep 03 '19 at 16:09
You can try `SELECT * INTO TMPTABLEFORUSERSUPDATING FROM WUSER WHERE 1 = 0` — Eric, Sep 03 '19 at 16:23

score 0 · Accepted Answer · answered Sep 03 '19 at 16:14

0

try this

CREATE TABLE newtable SELECT * FROM oldtable

Without AS

answered Sep 03 '19 at 16:14

GiovaniSalazar

Error Number:156,State:1,Class:15 Incorrect syntax near the keyword 'SELECT'. – SwagiWagi Sep 03 '19 at 16:16
CREATE TABLE new_tbl LIKE orig_tbl; – GiovaniSalazar Sep 03 '19 at 16:24
https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html – GiovaniSalazar Sep 03 '19 at 16:30
The problem was already solved + I'm not using MySql. – SwagiWagi Sep 03 '19 at 16:30

1 Answers1