CREATE TABLE - BY COPYING ALL COLUMNS FROM ANOTHER TABLE
lazarus / 16-Databases

----------------------------------------------------------------------
dkjMusic · 2014-09-09 18:01

I read (http://www.techonthenet.com/sql/tables/create_table2.php) that
this is done as

The syntax for the SQL CREATE TABLE AS statement copying all of the
columns is:
CREATE TABLE new_table
  AS (SELECT * FROM old_table);

With the following statement (Template is an empty table with the
columns I want) I get a SYNTAX ERROR in Create statement error (see
attached screenshot).

SQLQuery.SQL.Text := 'CREATE TABLE GeneralMerchandise AS (SELECT *
FROM Template)';

What am I doing wrong?

----------------------------------------------------------------------
mirce.vladimirov · 2014-09-09 20:23

Everyday I use only MySQL. I also use other DB occasionaly and I know
there are differences in SQL gramar among different DB's.
Solution for your example in MySQL it would be :
Code: [Select]CREATE TABLE new_table
  like  old_table ;This would create an empty new_table with identical
structure of fields, keys etc... If you need data to be written from
the old table  then, after creation :
Code: [Select]insert into new_table
select * from old_table where....

----------------------------------------------------------------------
taazz · 2014-09-09 20:47

I don't know the specific create variant to tell you the truth I have
never heard of it before so its either specific to a server I never
used or its something new. I always used the select into statement to
create a new table and move data to it in one go. Be ware that select
into will raise an error if the "newtable" already exists in the
server in that case you need to use insert into X select from......

----------------------------------------------------------------------
BigChimp · 2014-09-10 06:28

Quote from: dkjMusic on September 09, 2014, 08:01:43 pmThe syntax for
the SQL CREATE TABLE AS statement copying all of the columns is:
CREATE TABLE new_table
  AS (SELECT * FROM old_table);
No, it isn't.

SQL comes in multiple flavours:
1. Standard SQL (as specified in the... ehrm SQL standard)
2. Extensions/proprietary SQL that can be different per database
vendor (e.g. Oracle, Firebird, MySQL,...)
Each database has a base of 1. with a sauce of 2.

AFAIR, the statement you quote is NOT standard SQL.

That means it applies to a certain database. And you didn't mention
that. Or your Lazarus version, or your operating system or any code
snippet. Are you using SQLQuery.ExecSQL? .Open? (see first link in my
signature).

So, we're missing information and having to guess...
That makes it hard to help you.

----------------------------------------------------------------------
BigChimp · 2014-09-10 07:11

So your screenshot shows you're using MS Access via ODBC. Ok.

Have you tried running that command in Access itself? Does it run ok?
If not, please refer to the MS Access help on what the syntax is.
Edit: it probably is something like
select *  into GeneralMerchandise from Template;

----------------------------------------------------------------------
dkjMusic · 2014-09-10 11:25

BigChimp, I'm sorry.

I'll read the link in your signature. And as re my own signature, I
tried to specify my operating system, etc. there.

I'll try to do better.

----------------------------------------------------------------------
BigChimp · 2014-09-10 12:50

No, dkjMusic, I'm very sorry.

I read your post while I wasn't logged in so I could not read the
information in your link.
But that's no excuse.

Hope the SQL in my last post works for you.