Create SQL table as copy of an existing table (ODBC and C#)

Question

Using ODBC to manipulate an Access database in C#.

All of the following do NOT create the table. Each generates the exception Syntax error in CREATE TABLE statement.:

sqlQuery = "CREATE TABLE SpecialScans AS (SELECT * FROM Scans);"
sqlQuery = "CREATE TABLE [SpecialScans] AS (SELECT * FROM [Scans]);"
sqlQuery = "CREATE TABLE [SpecialScans] AS SELECT * FROM [Scans];"
// And all of the above with " WHERE 1=2;" to create just the schema

This follows the syntax specified on the w3schools website and elsewhere.

But the syntax given in the accepted answer on this question works fine:

sqlQuery = "SELECT * INTO [SpecialScans] FROM [Scans];"
// And with " WHERE 1=2;" to create just the schema

Why does the former fail but the latter work?

Answer 1

As pointed out, be it Oracle, MySQL, SQL server, FoxPro etc. etc. etc.?

The commands tend to differ by QUITE LARGE of a margin.

While for a simple SELECT statement, and even quite much most SQL joins, most databases that support SQL are quite simular for things like SELECT's.

However, when it comes to creating tables - then databases REALLY start to show their differances. Some have specific types of columns, many have different types of indexes - the list is VERY long.

While JET/ACE (the MS-Access database does claim ansi-92 compatiblity, the standards don't define say WHEN you want to copy a table for example - that a big rabbit hole - each vendor has differing means.

However, the JET/ACE Access syntax for what we call a make table query?

This code will work:

public void Test()
{
// make table query
string strSQL = "SELECT tblHotels.* INTO tblHotelsCopy FROM tblHotels";

using (OdbcCommand cmdSQL = new OdbcCommand(strSQL, new OdbcConnection(My.Settings.TestAccessDB)))
{
    cmdSQL.Connection.Open();
    cmdSQL.ExecuteNonQuery();
}
}

So in above, we using SELECT -> INTO. (and that syntax actually works on SQL server also).

so that sql as per above, works with SQL server, also works with MS-Access.

In above we are sending the table to a NEW TABLE. Hence this is what we would call a make table.

There is also the ability to "append" data from one table to the other table. This would thus allow you to send/transfer data from one table to another (existing) table.

This works in Access SQL but NOT sql server!!!

INSERT INTO tblHotelsCopy SELECT * FROM tblHotels

In sql server, you in general HAVE to specify all the columns - but in Access SQL you don't! - so this is a example of how Access SQL is somewhat more flexible.

So above is now NOT a MAKE-table, since the target table MUST exist. So, in above we inserting rows from tblHotels to tblhotelsCopy - they will be appended.

So, you want to VERY careful keep in mind

 MAKE table query - that creates the new target table

 APPEND table query - that sends a select of rows from one table into EXISTING table.