Syntax error while creating table from another table

Question

My code:

    Statement stmt=null;
    String cmdstr = "create table " + tableName + " as (select * from Master_Sheet);";

        try{            
            stmt = con.createStatement();
            stmt.executeUpdate(cmdstr);

            }
        catch(Exception e)
            {
            e.printStackTrace();
            }
        finally
        {
            try{
                if(stmt != null)
                stmt.close();
            }
            catch(Exception e)
            {
                e.printStackTrace();
            }
        }

Output:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

Please help, i am very new to java coding.

Answer 1

For Access, the syntax for creating a new table based on the data of a query is:

SELECT INTO newTable
FROM oldTable;

So your code should be rewritten as:

String cmdstr = "insert into table " + tableName + " From Master_Sheet;";

Make sure that your SQL statement follow the MS Access syntax before you use them in your java code.