Fastest way to copy sql table

Question

Im looking for the fastest way to copy a table and its contents on my sql server just simple copy of the table with the source and destination on the same server/database.

Currently with a stored procedure select * into sql statement it takes 6.75 minutes to copy over 4.7 million records. This is too slow.

CREATE PROCEDURE [dbo].[CopyTable1]
AS
BEGIN
DECLARE @mainTable VARCHAR(255),
        @backupTable VARCHAR(255),
        @sql VARCHAR(255),
        @qry nvarchar(max);

SET NOCOUNT ON;

Set @mainTable='Table1'
Set @backupTable=@mainTable + '_Previous'
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@backupTable) AND type in (N'U'))
BEGIN
    SET @Sql = 'if exists (select * from sysobjects '
    SET @Sql = @Sql + 'where id = object_id(N''[' + @backupTable + ']'') and '
    SET @Sql = @Sql + 'OBJECTPROPERTY(id, N''IsUserTable'') = 1) ' + CHAR(13)
    SET @Sql = @Sql + 'DROP TABLE [' + @backupTable + ']'
    EXEC (@Sql)
END

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@mainTable) AND type in (N'U'))
    SET @Sql = 'SELECT * INTO dbo.[' + @backupTable + '] FROM dbo.[' + @mainTable + ']'
    EXEC (@Sql)
END

Answer 1

If you are concerned about speed, it seems you have two alternatives; copying by block or the BCP/Bulk insert method.

Block Transfer

DECLARE 
    @CurrentRow bigint, @RowCount bigint, @CurrentBlock bigint

SET 
    @CurrentRow = 1

SELECT 
    @RowCount = Count(*)
FROM 
    oldtable 
WITH (NOLOCK)

WHILE @CurrentRow < @RowCount
BEGIN
    SET 
        @CurrentBlock = @CurrentRow + 1000000

INSERT INTO 
    newtable 
(FIELDS,GO,HERE)
SELECT 
    FIELDS,GO,HERE
FROM (
    SELECT 
        FIELDS,GO,HERE, ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum 
    FROM 
        oldtable
    WITH (NOLOCK)
    ) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN @startRow AND @endRow

SET 
    @CurrentRow = @CurrentBlock + 1
end

How to copy a huge table data into another table in SQL Server

BCP/Bulk Insert

SELECT 
    * 
INTO 
    NewTable 
FROM 
    OldTable 
WHERE 
    1=2

BULK INSERT 
    NewTable 
FROM 
    'c:\temp\OldTable.txt' 
WITH (DATAFILETYPE  = 'native')

What is the fastest way to copy data from one table to another

http://www.databasejournal.com/features/mssql/article.php/3507171/Transferring-Data-from-One-Table-to-Another.htm

Answer 2

You seem to want to copy a table that is a heap and has no indexes. That is the easiest case to get right. Just do a

insert into Target with (tablock) select * from Source

Make sure, that minimal logging for bulk operations is enabled (search for that term). Switch to the simple recovery model.

This will take up almost no log space because only allocations are logged with minimal logging.

This just scans the source in allocation order and append new bulk-allocated pages to the target.

Again, you have asked about the easiest case. Things get more complicated when indexes come into play.

Why not insert in batches? It's not necessary. Log space is not an issue. And because the target is not sorted (it is a heap) we don't need sort buffers.