How do I copy a table with Jet SQL? 1

[hoobly]

I have been writing SQL for DB2 and MySQL for years but I'm new to the Jet SQL dialect. Can I copy a table with Jet SQL and, if yes, how do I do it?

In DB2, if I have a table named Test and want a new empty table named Prod that has the same columns and datatypes, I can do this and the new table will be created:

CREATE TABLE PROD LIKE TEST

The cloned table, Prod, will be empty.

What is the equivalent in Jet SQL?

I looked at the DevGuru Jet SQL reference and I can't find anything resembling the LIKE clause that can be used to duplicate a table so I'm not sure if what I want to do is even possible. If the DevGuru Jet SQL Reference complete or is it only a few of the most commonly used clauses? If it is not complete, can anyone point me to a free online reference that includes documentation about the COMPLETE Jet SQL language?

I am writing a Java program that will connect with an Access database via the JDBC/ODBC driver and clone an existing table, then copy the data from the original table to the clone, then query and update the clone in various ways. I have already created and populated the original table, now I need to know how to clone the table definition to make an empty table, then all or some of the data from the original table to the clone.

If anyone can give me any pointers on how to do this, I'd appreciate it. I'm using Access 2002 (SP3).

 

[Golom]

Usually

Select * INTO Prod
From OldTable
Where PrimaryKey = 'SomeValueThatDoesNotExist'

or

Select * INTO Prod
From OldTable
Where 1=2

The first form tends to be a bit faster because Access can use an Index to filter records. In the second form Access has to check the condition for every record (i.e. a sequential scan.)

If you want to do it all in one step then you can use a suitable WHERE clause to specify the records that you want copied to the new table.

Note that the INTO clause does create the new table but it does not create corresponding keys or indexes.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]