Hi Team,
   I want to create a copy of Table A as Table B, including constraints and indexes.
   I have tried..

   {sql}
   CREATE TABLE TableB (LIKE TableA)
   {sql}

   and..

   {sql}
   CREATE TABLE TableB AS SELECT * FROM TableA WITH NO DATA
   {sql}

   In the former I get the constraints but no general indexes.
   In the latter get no constraints or indexes.
   Is that right?
   If yes, how do I duplicate TableA to TableB, including constraints and indexes?

Regards & TIA,
Ian

Ian

Unfortunately ElevateDB doesn't provide that feature. The information is all there in the system tables so you could further modify the script Tim gave me yonks ago

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END


my addition was to create one index. It wouldn't be to difficult to loop and get all of the tables indices/constraints

Roy Lambert

Hi Roy,
   Thanks for that.  Much appreciated.
   I wonder if Tim would be amenable to a change to accomodate??  We'll see.
Regards,
Ian

Ian

Try this script - if you like it I'll enhance it to include triggers


SCRIPT (IN NewTable VARCHAR, IN OldTable VARCHAR, IN Constraints BOOLEAN, IN Indices BOOLEAN, IN WithData BOOLEAN)
BEGIN
DECLARE Creator VARCHAR;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;
DECLARE Cntr INTEGER;
DECLARE LoopControl BOOLEAN;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING NewTable;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "' + NewTable+'"';
END IF;

CLOSE InfoCursor;
CLOSE ResultCursor;

IF Constraints THEN
 PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
 OPEN InfoCursor USING OldTable;
 FETCH FIRST FROM InfoCursor ('CreateSQL') INTO Creator;
 SET Creator = REPLACE('CREATE TABLE "' +OldTable + '"' WITH 'CREATE TABLE "' + NewTable +'"' IN Creator);
 EXECUTE IMMEDIATE Creator;
ELSE
 PREPARE ResultStmt FROM 'CREATE TABLE "' + NewTable + '" AS SELECT * FROM ' + OldTable + ' WITH NO DATA';
 EXECUTE ResultStmt;
END IF;

CLOSE InfoCursor;
CLOSE ResultCursor;

IF WithData THEN
 PREPARE ResultStmt FROM 'INSERT INTO '+ NewTable + ' SELECT * FROM ' + OldTable;
 EXECUTE ResultStmt;
END IF;

CLOSE InfoCursor;
CLOSE ResultCursor;   

SET LoopControl = TRUE;
SET Cntr = 0;

IF Indices THEN
 WHILE LoopControl DO
  PREPARE ResultStmt FROM 'SELECT CreateSQL FROM Information.Indexes WHERE OwnerConstraint IS NULL AND TableName = ?';
  OPEN ResultCursor USING OldTable;
  IF ROWCOUNT(ResultCursor) > 0 THEN
   FETCH RELATIVE Cntr FROM ResultCursor ('CreateSQL') INTO Creator;
   SET Creator = REPLACE('ON "' + OldTable + '"' WITH 'ON "' + NewTable +'"' IN Creator);
   EXECUTE IMMEDIATE Creator;
   SET Cntr = Cntr + 1;
   IF Cntr >= ROWCOUNT(ResultCursor) THEN
    SET LoopControl = FALSE;
   END IF;
   CLOSE ResultCursor;
  END IF;
 END WHILE;
END IF;

CLOSE InfoCursor;
CLOSE ResultCursor;

END

Roy Lambert

Gobsmacked.  That's what I am.

I am going to study this before I try it.
Like to understand what is going on.

Thank you very much.

Regards,
Ian

Hi Roy,
   Genius!  Works perfectly.
   Thank you very much.
   Suggestion if I may be so bold..
   Add another parameter - DropIfNewExists Boolean.  If the destination table, "NewTable", exists and the flag is true,
drop the destination table first.
   Just a thought.

Regards,
Ian

Ian

>    Suggestion if I may be so bold..
>    Add another parameter - DropIfNewExists Boolean. If the destination table, "NewTable", exists and the flag is true,
>drop the destination table first.
>    Just a thought.

That's what this

<<
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING NewTable;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "' + NewTable+'"';
END IF;
>>
does, just without the flag, and its a necessity since trying to create an existing table would cause the script to bomb.

Roy

Gulp!  Totally missed it.
Color me red all over.

Ian


>Color me red all over.

If you were a girl I might be willing to give it a go, but as a boy you're on your own!

Roy

Ian,

<< I wonder if Tim would be amenable to a change to accomodate?? >>

It's on the roadmap still, and I got half of it done a while ago (I think EDB will actually parse the syntax, but not do anything), so I'll see where things stand.  The main issue with using LIKE in that manner was the fact that a lot of internal references in the catalog needed to be reset, and there was some issue that was complicating the whole process.

Tim Young
Elevate Software
www.elevatesoft.com