Can someone give me an example of the syntax to clone a table indices, data, constraints and all?


Roy Lambert

Roy,

<< Can someone give me an example of the syntax to clone a table indices,
data, constraints and all? >>

Use this;

SCRIPT (NewTable VARCHAR, TableToCopy VARCHAR)
BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE "'+NewTable+'"
                    (
                    LIKE "'+TableToCopy+'" INCLUDING IDENTITY INCLUDING
DEFAULTS INCLUDING GENERATED
                    )';
  EXECUTE IMMEDIATE 'INSERT INTO "'+NewTable+'" SELECT * FROM
"'+TableToCopy+'"';
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


I saw those options in the manual, but didn't, and still don't understand what they each mean, and I certainly didn't figure any of them to mean indices.

Is there a reason for the LIKE rather than AS SELECT .. WITH DATA?

Roy Lambert

Roy,

<< I saw those options in the manual, but didn't, and still don't understand
what they each mean, and I certainly didn't figure any of them to mean
indices. >>

Actually, you're correct.  For some reason I thought that it would copy at
least the constraints also, but it isn't doing so.  I'll have to
double-check on this, because it may be a bug (the constraints).  At the
very least, what I'll do is include additional options for including the
constraints and/or indexes.

<< Is there a reason for the LIKE rather than AS SELECT .. WITH DATA? >>

Yes.  SELECT..WITH DATA does not copy default values, identify attributes,
etc.  It only copies the basic column information like the data type.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Actually, you're correct.  For some reason I thought that it would copy
at least the constraints also, but it isn't doing so.  I'll have to
double-check on this, because it may be a bug (the constraints).  >>

My guess is that you excluded the constraints because this is how the LIKE
clause is defined in the standard SQL specification.

<< At the very least, what I'll do is include additional options for
including the constraints and/or indexes. >>

Implementing support for constraints and indexes as additional options
(vendor extension) would be a good way IMO.

Ole Willy Tuv

Ole,

<< My guess is that you excluded the constraints because this is how the
LIKE clause is defined in the standard SQL specification. >>

Yes, that is likely.

<< Implementing support for constraints and indexes as additional options
(vendor extension) would be a good way IMO. >>

Yes, there needs to be a way to copy an entire table structure rather
easily.

--
Tim Young
Elevate Software
www.elevatesoft.com