Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 2 of 2 total |
How to Copy Tables Using New CreateSQL System Information in 2.12 and Higher |
Thu, Mar 21 2013 3:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Hi all,
This came up today as a support question, so I thought I'd share it with everyone. The attached procedure shows how to copy entire tables, including data, indexes, and triggers, using the new system information available in 2.12 and higher. Enjoy ! Tim Young Elevate Software www.elevatesoft.com ================================== CREATE PROCEDURE CopyTable(IN SourceTableName VARCHAR, IN DestTableName VARCHAR, IN IncludeData BOOLEAN) BEGIN DECLARE InfoCursor CURSOR FOR InfoStmt; DECLARE SQL VARCHAR; SET STATUS MESSAGE TO 'Creating table '+QUOTEDSTR(DestTableName,'"')+'...'; PREPARE InfoStmt FROM 'SELECT CreateSQL INTO ? FROM Information.Tables WHERE Name=?'; OPEN InfoCursor USING SQL,SourceTableName; IF SQL IS NOT NULL THEN SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'), QUOTEDSTR(DestTableName,'"'), SQL); EXECUTE IMMEDIATE SQL; END IF; IF IncludeData THEN SET STATUS MESSAGE TO 'Copying rows for table '+QUOTEDSTR(DestTableName,'"')+'...'; SET SQL = 'INSERT INTO '+QUOTEDSTR(DestTableName,'"')+' SELECT * FROM '+QUOTEDSTR(SourceTableName,'"'); EXECUTE IMMEDIATE SQL; END IF; PREPARE InfoStmt FROM 'SELECT * FROM Information.Indexes WHERE TableName=? AND '+ 'Type IN (''Index'',''Text Index'')'; OPEN InfoCursor USING SourceTableName; FETCH FIRST FROM InfoCursor ('CreateSQL') INTO SQL; SET STATUS MESSAGE TO 'Creating indexes for table '+QUOTEDSTR(DestTableName,'"')+'...'; WHILE NOT EOF(InfoCursor) DO SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'), QUOTEDSTR(DestTableName,'"'), SQL); EXECUTE IMMEDIATE SQL; FETCH NEXT FROM InfoCursor ('CreateSQL') INTO SQL; END WHILE; PREPARE InfoStmt FROM 'SELECT * FROM Information.Triggers WHERE TableName=?'; OPEN InfoCursor USING SourceTableName; FETCH FIRST FROM InfoCursor ('CreateSQL') INTO SQL; SET STATUS MESSAGE TO 'Creating triggers for table '+QUOTEDSTR(DestTableName,'"')+'...'; WHILE NOT EOF(InfoCursor) DO SET SQL = REPLACE(QUOTEDSTR(SourceTableName,'"'), QUOTEDSTR(DestTableName,'"'), SQL); EXECUTE IMMEDIATE SQL; FETCH NEXT FROM InfoCursor ('CreateSQL') INTO SQL; END WHILE; END ================================== Attachments: copytableproc.zip |
Sun, Mar 24 2013 6:43 AM | Permanent Link |
Adam Brett Orixa Systems | Excellent Tim, thanks. I'll have to download 2.12 now
|
This web page was last updated on Monday, April 29, 2024 at 05:23 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |