title: Fastest way to copy sql table
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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
  </code></pre>