Product Suggestion: CREATE TABLE AS CLONE

[Breck_Carter]

The CLONE feature would allow you to create a new table with the same schema as an existing table. This would have two main uses:

1. Create an ad-hoc clone of a production table for development, performance tuning and other temporary purposes.

2. Write SQL code which automates the creation of a clone as part of complex application processing. This is an important feature that would reduce maintenance effort: when the schema of the source table changes, the target schema would automatically change along with it.

A quick Google search shows that lots of folks have asked "how do I clone a table"... and if the 90-9-1 rule holds that means ten times as many WANT this feature .

The CLONE option should allow the target table to have different scope, persistency, etc; e.g., CLONE a temporary table from a permanent table.

CLONE options might include the following choices:

1. Copy all the data (yes/none).

2. Create all the same non-constraint indexes (yes/none).

3. Create the same primary key (yes/no).

4. Create all the same NOT NULL column constraints (yes/none).

5. Create all the same other non-foreign-key column and table constraints (yes/none)... note that foreign key support is NOT requested.

To make (thinking about) programming easier the default should be "yes" for all the options; i.e., create a full clone.

The temptation to add a WHERE clause and/or a column name list to the copy option should be resisted. There are lots of other powerful ways to copy some of the data after the table is created.

The temptation to fret about foreign key relationships should also be avoided... clones don't belong to any family at birth, but they can be adopted later on (ALTER TABLE).

The performance of the copy operation is not a concern; if folks want to fret about speed they can do the copy themselves, after the clone is created.

There are other complications (ON COMMIT/NOT TRANSACTIONAL, PCTFREE, etc)... the suggestion is to create an 80% solution that is nevertheless a lot better than the monstrous or cheesy solutions offered by other RDBMS products and third-party utilities (seriously, someone SELLs a clone utility? Yes, they do 🙂

[former_SQLA_member1694868]

We would use this as well for a few different purposes. I could clone a table before updating data as a quick backup rather than creating an image of the entire database. I could also see it being useful when I need to move data between two customer databases and want to check the results without affecting their production tables.

However, the statement "clones don't belong to any family at birth" troubles me a bit. If we do not give clones the same rights and respect we give actual tables, they may rise up and take over.

[Breck_Carter]

Oh, yes, we must treat clones much better than replicants: http://www.imdb.com/title/tt0083658/

[VolkerBarth]

I fully agree - particularly on the

default [] "yes" for all the options.

[Former Member · Accepted solution]

Thanks for the suggestion, Breck. We have been considering implementing exactly this sort of thing for some time now, and it is in our project plans - but I cannot commit to a particular release as yet.

[Breck_Carter · Accepted solution]

Can you commit to "no monstrosities" and "no cheese"? Yes, you can, you do things the way they should be done.

[Breck_Carter · Accepted solution]

Vote-wise, for this forum, it seems to be a "landslide"... especially when you consider the 90-9-1 rule 🙂

[Former Member]

This inspired me to a quick and dirty clone-table-script:

--Clone table
--Table must have a primary key
--Does not clone:
--   - Triggers
--   - Text indexes
--   - Comments on PK, Triggers and Text indexes
BEGIN
DECLARE @Sep VARCHAR(2)
SET @Sep = ';'
DECLARE @Table VARCHAR(100)
SET @Table = 'Calendar'
DECLARE @NewTable VARCHAR(100)
SET @NewTable = 'Calendar_new'

SELECT sql FROM (
SELECT '0' as sort, '-- Clone table ' as sql
UNION
SELECT '1', '  -- just comment out the blocks you don''t need.'
UNION
SELECT '2', '-- Creating table'
UNION
--Table
SELECT 'A' as sort, 'CREATE TABLE ' || @NewTable || ' ( '
UNION
  --Columns
SELECT 'B' || sys.syscolumn.object_id, CHAR(9) || '"' || trim(sys.syscolumn.column_name) || 
       '" ' || Upper(trim(sys.sysdomain.domain_name)) || 
       IF sys.syscolumn.nulls = 'N' THEN ' NOT NULL' ELSE ' NULL' END IF ||
       IF sys.syscolumn."default" is null THEN '' ELSE ' DEFAULT ' || Upper(Trim(sys.syscolumn."default")) END IF ||
       ','
   FROM sys.systable,
        sys.syscolumn,
        sys.sysuserperm,
        sys.sysdomain
  WHERE sys.systable.table_id = sys.syscolumn.table_id
    AND sys.systable.creator = sys.sysuserperm.user_id
    and sys.syscolumn.domain_id = sys.sysdomain.domain_id
    AND sys.systable.table_name = @Table
UNION
  --PKEY
SELECT 'C', CHAR(9) || 'PRIMARY KEY ("' || Upper(Trim(sys.syscolumn.column_name)) || '" ASC))' || @Sep
   FROM sys.systable,
        sys.syscolumn,
        sys.sysuserperm,
        sys.sysdomain
  WHERE sys.systable.table_id = sys.syscolumn.table_id
    AND sys.systable.creator = sys.sysuserperm.user_id
    and sys.syscolumn.domain_id = sys.sysdomain.domain_id
    AND sys.systable.table_name = @Table
    AND sys.syscolumn.pkey = 'Y'
UNION
SELECT 'D', '-- Creating indexes'
UNION
--Indexes
SELECT 'D1', 'CREATE INDEX "' || Trim(sys.sysindex.index_name) || '" ON ' || @NewTable || ' ( "' || Trim(sys.sysindex.index_name) || '" ASC )' || @Sep
   FROM sys.systable,
        sys.sysindex
  WHERE sys.systable.table_name = @Table
    AND sys.systable.table_id = sys.sysindex.table_id
UNION
SELECT 'E', '-- Granting permissions'
UNION
--Permissions
SELECT 'E1', 'GRANT ' || IF sys.systableperm.selectauth = 'Y' THEN 'SELECT, ' END IF ||
                        IF sys.systableperm.insertauth = 'Y' THEN 'INSERT, ' END IF ||
                        IF sys.systableperm.deleteauth = 'Y' THEN 'DELETE, ' END IF ||
                        IF sys.systableperm.updateauth = 'Y' THEN 'UPDATE, ' END IF ||
                        IF sys.systableperm.alterauth = 'Y' THEN 'ALTER, ' END IF ||
                        IF sys.systableperm.referenceauth = 'Y' THEN 'REFERENCES , ' END IF ||
                        'ON ' || @NewTable ||
                        ' TO ' || Trim(sys.sysuserperm.user_name) || @Sep
   FROM sys.systable,
        sys.sysuserperm,
        sys.systableperm
  WHERE sys.systable.table_name = @Table
    AND sys.systable.table_id = sys.systableperm.stable_id
    AND sys.systableperm.grantee = sys.sysuserperm.user_id
UNION
SELECT 'F', '-- Putting on comments'
UNION
--Comments on
  --Table
SELECT 'F1','COMMENT ON TABLE ' || @NewTable || ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
INNER JOIN sys.systable ON sys.systable.object_id = sys.sysremark.object_id
WHERE sys.systable.table_name = @Table
UNION
  --Columns
SELECT 'F1','COMMENT ON COLUMN ' || @NewTable || '.' || sys.syscolumn.column_name
|| ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
LEFT JOIN sys.syscolumn ON sys.syscolumn.object_id = sys.sysremark.object_id
WHERE sys.syscolumn.table_id = (select table_id from sys.systable where table_name = @Table) 
UNION
  --Index
SELECT 'F1','COMMENT ON INDEX ' || @NewTable || '.' 
|| sys.sysindex.index_name || ' IS "' || sys.sysremark.remarks || '"' || @Sep
FROM sys.sysremark INNER JOIN sys.sysobject ON sys.sysobject.object_id = sys.sysremark.object_id
LEFT JOIN sys.sysindex ON sys.sysindex.object_id = sys.sysremark.object_id
WHERE sys.sysindex.table_id = (select table_id from sys.systable where table_name = @Table) 
UNION
--Copy data
SELECT 'G', '-- Copying data to new table'
UNION
SELECT 'G0', '  -- As an alternative you could generate SQL insert statements for a result set in ISQL'
UNION
SELECT 'G01', '  -- To generate SQL statements from an Interactive SQL result set'
UNION
SELECT 'G02', '  -- Select the row(s) you want to generate a statement for.'
UNION
SELECT 'G03', '  -- Right-click the selection, and choose Generate, and then choose INSERT Statement, DELETE Statement, or UPDATE Statement.'
UNION
SELECT 'G04', '  -- The statement is copied to the clipboard.'
UNION
SELECT 'G05', '  -- PS: If @NewTable is different from @Table, you will have to search''n replace in the generated sql statements.'
UNION
SELECT 'G1', 'INSERT INTO ' || @NewTable || ' WITH AUTO NAME (SELECT * FROM ' || @Table || ')' || @Sep
Order by sort asc
) Clone
END

Feel free to optimize, improve and extend:-)

[MarkCulp]

You can now create a table like another table using SQL Anywhere version 17 - see the v17 documentation.