How to Copy Indexes from One Table to Another in SQL Server

How to copy indexes from one table to another in SQL Server

Do you want to copy the Index definition?

Then you can reverse engineer the index, triggers etc using the "Script" option in the Microsoft SQL Management tool

Simply right click on a table name in the SQL Management Studio table list and select "Script Table as" and then "Create to"

You can't copy the Index data as it relates to the physical storage of the Index

First check that you have "Tools/Options/SQL Server Object Explorer/Scripting/Script Indexes" set to "True". This is set to false in some version of the SQL Management tool (thanks Mark)

Programmatically copy indexes from one table to another in SQL Server

This is what I came up with. It works for me and copies all the stuff I care about.

CREATE PROCEDURE [dbo].[spCloneTableStructure]
    @SourceSchema nvarchar(255),
    @SourceTable nvarchar(255),
    @DestinationSchema nvarchar(255),
    @DestinationTable nvarchar(255),
    @RecreateIfExists bit = 0
AS
BEGIN
    /*
        Clones an existing table to another table (without data)
        Optionally drops and re-creates target table
        Copies:
            * Structure
            * Primary key
            * Indexes (including ASC/DESC, included columns, filters)
            * Constraints (and unique constraints)

        DOES NOT copy:
            * Triggers
            * File groups
            * Probably a lot of other things

        Note: Assumes that you name (unique) constraints with the table name in it (in order to not duplicate constraint names)
    */
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    --drop the table
    if EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @DestinationSchema AND TABLE_NAME = @DestinationTable)
    BEGIN
        if @RecreateIfExists = 1
        BEGIN
            exec('DROP TABLE [' + @DestinationSchema + '].[' + @DestinationTable + ']')
        END
        ELSE
            RETURN
    END

    --create the table
    exec('SELECT TOP (0) * INTO [' + @DestinationSchema + '].[' + @DestinationTable + '] FROM [' + @SourceSchema + '].[' + @SourceTable + ']')

    DECLARE @PKSchema nvarchar(255), @PKName nvarchar(255)
    SELECT TOP 1 @PKSchema = CONSTRAINT_SCHEMA, @PKName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @SourceSchema AND TABLE_NAME = @SourceTable AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    --create primary key
    IF NOT @PKSchema IS NULL AND NOT @PKName IS NULL
    BEGIN
        DECLARE @PKColumns nvarchar(MAX)
        SET @PKColumns = ''

        SELECT @PKColumns = @PKColumns + '[' + COLUMN_NAME + '],'
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
            where TABLE_NAME = @SourceTable and TABLE_SCHEMA = @SourceSchema AND CONSTRAINT_SCHEMA = @PKSchema AND CONSTRAINT_NAME= @PKName
            ORDER BY ORDINAL_POSITION

        SET @PKColumns = LEFT(@PKColumns, LEN(@PKColumns) - 1)

        exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [PK_' + @DestinationTable + '] PRIMARY KEY CLUSTERED (' + @PKColumns + ')');
    END

    --create other indexes
    DECLARE @IndexId int, @IndexName nvarchar(255), @IsUnique bit, @IsUniqueConstraint bit, @FilterDefinition nvarchar(max)

    DECLARE indexcursor CURSOR FOR
    SELECT index_id, name, is_unique, is_unique_constraint, filter_definition FROM sys.indexes WHERE type = 2 and object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']')
    OPEN indexcursor;
    FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            DECLARE @Unique nvarchar(255)
            SET @Unique = CASE WHEN @IsUnique = 1 THEN ' UNIQUE ' ELSE '' END

            DECLARE @KeyColumns nvarchar(max), @IncludedColumns nvarchar(max)
            SET @KeyColumns = ''
            SET @IncludedColumns = ''

            select @KeyColumns = @KeyColumns + '[' + c.name + '] ' + CASE WHEN is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END + ',' from sys.index_columns ic
            inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
            where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal > 0
            order by index_column_id

            select @IncludedColumns = @IncludedColumns + '[' + c.name + '],' from sys.index_columns ic
            inner join sys.columns c ON c.object_id = ic.object_id and c.column_id = ic.column_id
            where index_id = @IndexId and ic.object_id = object_id('[' + @SourceSchema + '].[' + @SourceTable + ']') and key_ordinal = 0
            order by index_column_id

            IF LEN(@KeyColumns) > 0
                SET @KeyColumns = LEFT(@KeyColumns, LEN(@KeyColumns) - 1)

            IF LEN(@IncludedColumns) > 0
            BEGIN
                SET @IncludedColumns = ' INCLUDE (' + LEFT(@IncludedColumns, LEN(@IncludedColumns) - 1) + ')'
            END

            IF @FilterDefinition IS NULL
                SET @FilterDefinition = ''
            ELSE
                SET @FilterDefinition = 'WHERE ' + @FilterDefinition + ' '

            if @IsUniqueConstraint = 0
                exec('CREATE ' + @Unique + ' NONCLUSTERED INDEX [' + @IndexName + '] ON [' + @DestinationSchema + '].[' + @DestinationTable + '] (' + @KeyColumns + ')' + @IncludedColumns + @FilterDefinition)
            ELSE
                BEGIN
                    SET @IndexName = REPLACE(@IndexName, @SourceTable, @DestinationTable)
                    exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] ADD  CONSTRAINT [' + @IndexName + '] UNIQUE NONCLUSTERED (' + @KeyColumns + ')');
                END

            FETCH NEXT FROM indexcursor INTO @IndexId, @IndexName, @IsUnique, @IsUniqueConstraint, @FilterDefinition;
       END;
    CLOSE indexcursor;
    DEALLOCATE indexcursor;

    --create constraints
    DECLARE @ConstraintName nvarchar(max), @CheckClause nvarchar(max)
    DECLARE constraintcursor CURSOR FOR
        SELECT REPLACE(c.CONSTRAINT_NAME, @SourceTable, @DestinationTable), CHECK_CLAUSE from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE t
        INNER JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS c ON c.CONSTRAINT_SCHEMA = TABLE_SCHEMA AND c.CONSTRAINT_NAME = t.CONSTRAINT_NAME
         WHERE TABLE_SCHEMA = @SourceSchema AND TABLE_NAME = @SourceTable
    OPEN constraintcursor;
    FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
    WHILE @@FETCH_STATUS = 0
       BEGIN
            exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] WITH CHECK ADD  CONSTRAINT [' + @ConstraintName + '] CHECK ' + @CheckClause)
            exec('ALTER TABLE [' + @DestinationSchema + '].[' + @DestinationTable + '] CHECK CONSTRAINT [' + @ConstraintName + ']')
            FETCH NEXT FROM constraintcursor INTO @ConstraintName, @CheckClause;
       END;
    CLOSE constraintcursor;
    DEALLOCATE constraintcursor;

    COMMIT TRANSACTION
END

Copy data into another table

If both tables are truly the same schema:

INSERT INTO newTable
SELECT * FROM oldTable

Otherwise, you'll have to specify the column names (the column list for newTable is optional if you are specifying a value for all columns and selecting columns in the same order as newTable's schema):

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

How to copy a SQL table to another server with index and keys?

Obviously there is a bug in Microsoft SSMS. With your method 1 SSMS 2016 still executes the standard CREATE TABLE query in the background, although you edit it in the wizard. My recommendation is that you download and install the latest SSMS version. Here is the link to it. After that you can use your method 1 without problems and it will work without problems.

Programmatically copy index from one table to another, via a stored procedure?

This is a really complicated thing to do in SQL code. It's complicated because indexes have so many options. Potential issues/complications:

• Covering indexes
• Included fields
• Column order
• Filtered indexes
• Complicated index types (xml indexes, etc)

You will be much better off either scripting these out in SSMS or using the .NET SMO framework for this.

To appropriately account for all the above issues will require querying a large number of system tables and using CURSORs, WHILE loops, or other iterative constructs to get things in the right order.

It ain't pretty.

How to copy large number of data from one table to another in same database?

First of all, disable the index on TableB before inserting the rows. You can do it using T-SQL:

ALTER INDEX IX_Index_Name ON dbo.TableB DISABLE;  

Make sure to disable all the constraints (foreign keys, check constraints, unique indexes) on your destination table.

Re-enable (and rebuild) them after the load is complete.

Now, there's a couple of approaches to solve the problem:

1. You have to be OK with a slight chance of data loss: use the INSERT INTO ... SELECT ... FROM ... syntax you have but switch your database to Bulk-logged recovery mode first (read before switching). Won't help if you're already in Bulk-logged or Simple.
2. With exporting the data first: you can use the BCP utility to export/import the data. It supports loading data in batches. Read more about using the BCP utility here.
3. Fancy, with exporting the data first: With SQL 2012+ you can try exporting the data into binary file (using the BCP utility) and load it by using the BULK INSERT statement, setting ROWS_PER_BATCH option.

4. Old-school "I don't give a damn" method: to prevent the log from filling up you will need to perform the
   inserts in batches of rows, not everything at once. If your database
   is running in Full recovery mode you will need to keep log backups
   running, maybe even trying to increase the frequency of the job.

   To batch-load your rows you will need a WHILE (don't use them in
   day-to-day stuff, just for batch loads), something like the
   following will work if you have an identifier in the dbo.TableA
   table:

   DECLARE @RowsToLoad BIGINT;
   DECLARE @RowsPerBatch INT = 5000;
   DECLARE @LeftBoundary BIGINT = 0;
   DECLARE @RightBoundary BIGINT = @RowsPerBatch;
   
   SELECT @RowsToLoad = MAX(IdentifierColumn) dbo.FROM TableA
   
   WHILE @LeftBoundary < @RowsToLoad
   BEGIN
       INSERT INTO TableB (Column1, Column2)
       SELECT
           tA.Column1,
           tB.Column2
       FROM
           dbo.TableA as tA
       WHERE
           tA.IdentifierColumn > @LeftBoundary
           AND tA.IdentifierColumn <= @RightBoundary
   
       SET @LeftBoundary = @LeftBoundary + @RowsPerBatch;
       SET @RightBoundary = @RightBoundary + @RowsPerBatch;
   END
   

   For this to work effectively you really want to consider creating an
   index on dbo.TableA (IdentifierColumn) just for the time you're
   running the load.

---

Related Topics