"Duplicate Table"

Apr 3, 2013 at 10:42 AM
This is similar to another post, but not identical.

I have a view, which I want to 'clone' into a Staging table (drop and recreate is fine). What is the most efficient way of doing this with this library.

I can do the following:
Retrieve the data from the view into a DataTable.
create a DBCreateTableQuery
loop through the columns calling the DBCreateTableQuery.Add method.
execute the DBCreateTableQuery.

Is this the best approach - was wondering whether there was a way without the data ending up within .net (i.e. all work is done on the SQL Server)?

G
Apr 4, 2013 at 12:58 PM
Sounds like there are 2 things here - the dynamic analysis of the table schema, and then the select into.
I did a quick test method
  1. using the SchemaProvider to get a table based on the name
  2. Build a DROP clone table if exists statement
  3. Build a CREATE clone table statement using the schema information
  4. Build an INSERT INTO and a SELECT FROM using the schema information
  5. Execute the DROP then CREATE
  6. Execute the INSERT
Work surprisingly well. Code is below.
            string tbl2Clone = "DQSL_COURSES";
            string cloneTbl = tbl2Clone + "_Clone";

            //your database instance
            DBDatabase db = this.Database;

            //use the schema provider to extract the name and column info
            Schema.DBSchemaProvider schema = db.GetSchemaProvider();
            Schema.DBSchemaTable tbl = schema.GetTable(tbl2Clone);
            

            DBCreateTableQuery createClone = DBCreateTableQuery.Create.Table(tbl.Schema, cloneTbl);
            DBDropTableQuery dropClone = DBDropTableQuery.Drop.Table(tbl.Schema, cloneTbl).IfExists();

            DBInsertQuery ins = DBQuery.InsertInto(cloneTbl);
            DBSelectQuery sel = DBSelectQuery.Select();

            foreach (Schema.DBSchemaTableColumn col in tbl.Columns)
            {
                //append the columns to the create statement
                if(col.Size > 0)
                    createClone.Add(col.Name, col.DbType, col.Size, col.ColumnFlags);
                else
                    createClone.Add(col.Name, col.DbType, col.ColumnFlags);

                //if we are not an auto assign then add the column to the select and the insert
                if (col.AutoAssign == false)
                {
                    sel.Field(col.Name);
                    ins.Field(col.Name);
                }
            }
            sel = sel.From(tbl.Schema, tbl.Name);
            //add any filtering to the select statement to limit the inserted rows

            //Append the select as part of the insert
            ins.Select(sel);

            //statement construction complete

            //drop any existing cloned table
            db.ExecuteNonQuery(DBQuery.Drop.Table(cloneTbl).IfExists());
                 // IF EXISTS (SELECT *
                //          FROM [INFORMATION_SCHEMA].[TABLES]
                //          WHERE  ([TABLE_NAME] = 'DQSL_COURSES_Clone') ) 
            //              DROP TABLE [DQSL_COURSES_Clone]

            db.ExecuteNonQuery(createClone);
                    //  CREATE TABLE [dbo].[DQSL_COURSES_Clone] (
            //              [CourseID] CHAR(4) PRIMARY KEY NOT NULL, 
            //              [Title] NVARCHAR(100) NOT NULL, 
            //              [Credits] FLOAT(4) NOT NULL, 
            //              [DepartmentID] INT NOT NULL, 
            //              [Description] NVARCHAR(1000) NULL)

            //execute the insert into select from
            db.ExecuteNonQuery(ins);
                 //  INSERT INTO [DQSL_COURSES_Clone]
            //          ([CourseID], [Title], [Credits], [DepartmentID], [Description])
            //              (SELECT [CourseID], [Title], [Credits], [DepartmentID], [Description]
        //               FROM [dbo].[DQSL_COURSES])