Syntax error in CREATE TABLE statement When using Create Table AS

Question

For a project i need to copy a table from a template, but when executing the SQL code the response is Syntax error in CREATE TABLE statement.

   OleDbConnection conn = new OleDbConnection();
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["LoginDataB"].ToString();
        OleDbCommand cmd = new OleDbCommand();
        cmd.Connection = conn;


    cmd.CommandText = "CREATE TABLE 'TEST' AS SELECT * INTO 'TEST' FROM 
    buttonstemplate";

This is the code i have used. Yet when I use an online SQL editor like W3schools It works just fine. I am using MS Access 2016 and programming in ASP.net

I am just starting to learn to code so I hope someone could help me out.

what happens when you add one more colon for all single colon values like 'TEST' to '''test''' — TheGameiswar
– TheGameiswar, Commented Dec 25, 2017 at 2:44
Is TEST a variable or actual name you want to assign the table? Did you test the SQL in Access query designer? @TheGameiswar, what colons and where? — June7
– June7, Commented Dec 25, 2017 at 2:46
Test is the name of name of the database i want to create, @June7 i did and it gives the same error. but like i said when using the W3school editor it works just fine so i assume its acces. — Johnny_C
– Johnny_C, Commented Dec 25, 2017 at 3:05

maddy23285 · Accepted Answer · 2017-12-25 08:19:16Z

1

Try this:

SELECT * INTO TEST FROM BUTTONSTEMPLATE

To copy table into another database for eg: externaldb.mdb

SELECT * INTO TEST IN 'externaldb.mdb' FROM BUTTONSTEMPLATE

edited Dec 25, 2017 at 8:19

answered Dec 25, 2017 at 8:13

maddy23285

7867 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ashleedawg · Accepted Answer · 2017-12-25 04:36:51Z

0

...I don't think that SQL runs fine in W3 Editor (like your question said). :-)

I'm sure you're aware that the first step is to make sure the SQL will run on it's own.

You're combining two different types of SQL statements for creating a table:

CREATE TABLE is for defining a new table. For example:

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

If you want to create a table based on the results from another table:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

...so perhaps you could use:

INSERT INTO TEST
SELECT * FROM buttonstemplate

answered Dec 25, 2017 at 4:36

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

Collectives™ on Stack Overflow

Syntax error in CREATE TABLE statement When using Create Table AS

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related