Split 100 million row table into multiple tables I have a SQL Server 2012 database. There is one table that has more than 100 million rows. I want to split that table into multiple similar tables, with 1 million rows in each table. What is the best way to achieve this quickly?

I have tried paging, but it takes too much time, and sometimes gives an out of memory exception in SSMS.

There are 12 columns. 11 are varchar and one is datetime. There is no primary key available in the table, and no ID column. It is a very old database table that we migrated to SQL Server 2012.

Its size is around 20 GB. There are no indexes available. I don't want more complexity, I just need to split it.
Yes, it is possible to split this 100 million row table into any number of tables, even without it having any indexes. Now, you might could do it purely in T-SQL but that would require loading each of the split tables via individual insert statements in a transaction, and that can be a bit heavy on the tran log, especially if splitting to tables of 2 million rows each.
But, thanks to the totally awesome awesomeness of the fully streamed API for Table-Valued Parameters (TVPs), a small (and relatively simple) C# / VB.Net (or any .Net language) app can handle this, and do so without using more than 1 row's worth of memory (on the app side; buffer pool on the DB is what it is), and do so using a minimally-logged operation! The trick is to:


call a stored procedure that accepts a TVP for the rows to move, and
pass the rows into the TVP by setting the SqlParameter value to a method that returns IEnumerable&lt;SqlDataRecord&gt;. This will allow for incrementally moving through a SqlDataReader that is reading the source records.
Using this method, the size of the source table shouldn't matter: splitting a 1 billion row table should only require more time (and the obvious disk space required to duplicate the table).


Database Objects

First: You need a User-Defined Table Type

CREATE TYPE dbo.SplitTable AS TABLE
(
   CreateDate DATETIME NOT NULL,
   StringField01 VARCHAR(500) NULL,
   StringField02 VARCHAR(500) NULL,
   ...
   StringField10 VARCHAR(500) NULL,
   StringField11 VARCHAR(500) NULL
);
GO

GRANT EXECUTE ON TYPE::[dbo].[SplitTable] TO [user_or_role];
GO


Second: Use the UDTT as an input param to an import proc. Hence "Tabled-Valued Parameter" (TVP)

-- DROP PROCEDURE dbo.ImportChunk
CREATE PROCEDURE dbo.ImportChunk (
   @TableNumber   INT,
   @SplitTable    dbo.SplitTable READONLY
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX),
        @CurrentTableName sysname;

BEGIN TRY

   -- Empty TVPs are not allowed, and can't send DbNull.Value when using
   -- IEnumerable&lt;SqlDataRecord&gt;, so send in a single-row with a field to test that would
   -- otherwise never have that value. Only need to test 1 row as real sets of rows
   -- won't have this value so no need to test the whole set.
   IF ((SELECT TOP (1) [CreateDate] FROM @SplitTable) = CONVERT(DATETIME, 0))
   BEGIN
      RETURN;
   END;

   BEGIN TRAN;

   SET @CurrentTableName = N'dbo.SplitTableName_'
                           + RIGHT(N'000' + CONVERT(NVARCHAR(20), @TableNumber), 4);
   PRINT 'CurrentTableName: ' + @CurrentTableName;

   SET @SQL = N'CREATE TABLE '
              + @CurrentTableName
              + N' (
CreateDate DATETIME NOT NULL,
StringField01 VARCHAR(500) NULL,
StringField02 VARCHAR(500) NULL,
...
StringField10 VARCHAR(500) NULL,
StringField11 VARCHAR(500) NULL
);';

   EXEC(@SQL);

   -- Use a SYNONYM as we are dealing with dynamic table names which requires Dynamic SQL
   -- but the TVP is a table variable and cannot be referenced in Dynamic SQL. However,
   -- a SYNONYM can be created in Dynamic SQL and is a consistent object name that can
   -- be used in the static / main portion of this code where the TVP can be used.
   -- http://msdn.microsoft.com/en-us/library/ms177544.aspx (CREATE SYNONYM)
   SET @SQL = N'CREATE SYNONYM CurrentImportTable FOR '
              + @CurrentTableName;

   EXEC(@SQL);

   -- The WITH (TABLOCK) hint is required for this query to be considered minimally logged.
   -- http://msdn.microsoft.com/en-us/library/ms174335.aspx (INSERT)
   INSERT INTO CurrentImportTable WITH (TABLOCK)
      (CreateDate, StringField01, StringField02, --...,
      StringField10, StringField11)
      SELECT tmp.CreateDate,
             tmp.StringField01,
             tmp.StringField02,
             ...
             tmp.StringField10,
             tmp.StringField11
      FROM   @SplitTable tmp;

   DROP SYNONYM CurrentImportTable; -- Clean up.

   COMMIT TRAN;

END TRY
BEGIN CATCH

   IF (@@TRANCOUNT &gt; 0)
   BEGIN
      ROLLBACK;
   END;

   DECLARE @Message NVARCHAR(4000);
   SET @Message = ERROR_MESSAGE();
   RAISERROR(@Message, 16, 1);

END CATCH;
GO

GRANT EXECUTE ON dbo.ImportData TO [user_or_role];


C# Code

Third: Open a SqlDataReader to a simple select on the table in question (essentially a cursor). The trick is to implement a method that will


read a set number of rows rather than until the end of the result set
pass each row, as it is read, to the TVP


Then just call the stored procedure shown above passing in the loop counter (so we can name the new table appropriately) and the method that returns only that set number of rows via IEnumerable. Keep calling the procedure until there are no more records in the result set / SqlDataReader.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Server;

namespace TableSplitter
{
    class Program
    {
        // these first two variables are static as their values need to be passed back to
        // the main loop but iterators (i.e. returning IEnumerable) cannot have "ref" parameters
        private static SqlDataReader _RowsToSend;
        private static int _RowsCopied;

        private static int _RowsPerSplit;

        private static IEnumerable&lt;SqlDataRecord&gt; SendRows()
        {
            SqlMetaData[] _TvpSchema = new SqlMetaData[]
            {
                new SqlMetaData("CreateDate", SqlDbType.DateTime),
                new SqlMetaData("StringField01", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField02", SqlDbType.VarChar, 500),
                //...
                new SqlMetaData("StringField10", SqlDbType.VarChar, 500),
                new SqlMetaData("StringField11", SqlDbType.VarChar, 500)
            };

            SqlDataRecord _DataRecord = new SqlDataRecord(_TvpSchema);
            object[] _AllFields = new object[12];

            // read a row, send a row
            for (_RowsCopied = 0; _RowsCopied &lt; _RowsPerSplit; _RowsCopied++)
            {
                if (!_RowsToSend.Read())
                {
                    break;
                }
                _RowsToSend.GetValues(_AllFields);
                _DataRecord.SetValues(_AllFields);

                yield return _DataRecord;
            }

            if (_RowsCopied == 0)
            {
                _DataRecord.SetDateTime(0, new DateTime(1900, 1, 1));
                yield return _DataRecord;
            }
        }

        public static void SplitTable(string ConnectionString)
        {
            _RowsCopied = _RowsPerSplit; // seed value to enter the loop

            int _TableNumber = 1;

            SqlConnection _SourceConnection = new SqlConnection(ConnectionString);
            SqlCommand _SourceCommand = new SqlCommand(
                "ALTER DATABASE [Test] SET RECOVERY BULK_LOGGED;",
                _SourceConnection);
            _SourceCommand.CommandType = CommandType.Text;

            SqlConnection _DestinationConnection = new SqlConnection(ConnectionString);
            SqlCommand _DestinationCommand = new SqlCommand(
                "dbo.ImportChunk",
                _DestinationConnection);
            _DestinationCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter _TVParam = new SqlParameter();
            _TVParam.ParameterName = "@SplitTable";
            _TVParam.SqlDbType = SqlDbType.Structured;
            _DestinationCommand.Parameters.Add(_TVParam);

            SqlParameter _TableNumParam = new SqlParameter();
            _TableNumParam.ParameterName = "@TableNumber";
            _TableNumParam.SqlDbType = SqlDbType.Int;
            _DestinationCommand.Parameters.Add(_TableNumParam);

            try
            {
                _SourceConnection.Open();
                _SourceCommand.ExecuteNonQuery();

                _SourceCommand.CommandText =
                    @"SELECT CreateDate, StringField01, StringField02, --...,
                    StringField10, StringField11 FROM dbo.TableToSplit;";
                _RowsToSend = _SourceCommand.ExecuteReader();

                _DestinationConnection.Open();

                while (_RowsCopied == _RowsPerSplit)
                {
                    _TableNumParam.Value = _TableNumber;
                    _TVParam.Value = SendRows(); // method return value is streamed data

                    _DestinationCommand.ExecuteNonQuery();

                    _TableNumber++;
                }
            }
            catch (Exception _Exception)
            {
                System.Console.WriteLine("\n\n" + _Exception.Message + "\n\n");
            }
            finally
            {
                _RowsToSend.Close();
                _DestinationConnection.Close();

                _SourceCommand.CommandText = 
                    "ALTER DATABASE [Test] SET RECOVERY SIMPLE;"; // or FULL
                _SourceCommand.ExecuteNonQuery();

                _SourceConnection.Close();
            }

            return;
        }


        static void Main(string[] args)
        {
            System.Diagnostics.Stopwatch _ElapsedTime = new System.Diagnostics.Stopwatch();
            _ElapsedTime.Start();

            _RowsPerSplit = 1000000;

            SplitTable("Trusted_Connection = yes; Database = Test;");

            _ElapsedTime.Stop();

            System.Console.WriteLine("\n\n\tElapsed Millseconds: {0}\n\n", 
                                      _ElapsedTime.ElapsedMilliseconds);
        }
    }
}




I ran this on my laptop with a sample table consisting of:


5 fields: 1 DATETIME and the other 4 are VARCHAR
3 million rows
205 GB


I ran it several times and it took about 19 - 24 seconds to split into 3 tables of 1 million rows each.