![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
![]() |
Mon, Jul 9 2018 11:48 PM | Permanent Link |
Ian Branch | 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 |
Tue, Jul 10 2018 2:44 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Jul 10 2018 3:12 AM | Permanent Link |
Ian Branch | Hi Roy,
Thanks for that. Much appreciated. I wonder if Tim would be amenable to a change to accomodate?? We'll see. ![]() Regards, Ian |
Tue, Jul 10 2018 5:35 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Tue, Jul 10 2018 6:54 AM | Permanent Link |
Ian Branch | 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 |
Tue, Jul 10 2018 5:10 PM | Permanent Link |
Ian Branch | 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 |
Wed, Jul 11 2018 2:21 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Jul 11 2018 4:54 AM | Permanent Link |
Ian Branch | Gulp! Totally missed it.
![]() Color me red all over. |
Wed, Jul 11 2018 8:32 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, Jul 13 2018 11:50 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |