Cómo copiar datos de una tabla enorme en otra tabla en SQL Server

Tuve el mismo problema, excepto que tengo una tabla con 2 mil millones de filas, por lo que el archivo de registro crecería sin fin si hiciera esto, incluso con el modelo de recuperación establecido en Registro masivo:

insert into newtable select * from oldtable

Así que opero en bloques de datos. De esta manera, si la transferencia se interrumpe, solo tiene que reiniciarla. Además, no necesita un archivo de registro tan grande como la tabla. También parece obtener menos E/S tempdb, no estoy seguro de por qué.

set identity_insert newtable on
DECLARE @StartID bigint, @LastID bigint, @EndID bigint
select @StartID = isNull(max(id),0) + 1
from newtable

select @LastID = max(ID)
from oldtable

while @StartID < @LastID
begin
    set @EndID = @StartID + 1000000

    insert into newtable (FIELDS,GO,HERE)
    select FIELDS,GO,HERE from oldtable (NOLOCK)
    where id BETWEEN @StartID AND @EndId

    set @StartID = @EndID + 1
end
set identity_insert newtable off
go

Es posible que deba cambiar la forma en que maneja los ID, esto funciona mejor si su tabla es agrupado por ID.

He estado trabajando con nuestro DBA para copiar una tabla de auditoría con filas de 240M a otra base de datos.

Usando un simple select/insert se creó un enorme archivo tempdb.

Usando un asistente de Importación / exportación trabajó pero copió filas de 8M en 10min

Creando un paquete SSIS personalizado y ajustando la configuración copiado 30M filas en 10Min

El paquete SSIS resultó ser el más rápido y eficaz para nuestros propósitos

Earl

Aquí hay otra forma de transferir tablas grandes. Acabo de transferir 105 millones de filas entre dos servidores usando esto. Muy rápido también.

1. Haga clic con el botón derecho en la base de datos y elija Tareas/Exportar datos.
2. Un asistente le guiará a través de los pasos, pero al elegir su cliente SQL Server como origen y destino de datos, le permitirá seleccionar la base de datos y las tablas que desea transferir.

Para más información, ver https://www.mssqltips.com/sqlservertutorial/202/simple-way-to-export-data-from-sql-server/

Si se trata de una importación de 1 vez, la utilidad de importación/exportación en SSMS probablemente funcionará de la manera más fácil y rápida. SSIS también parece funcionar mejor para importar grandes conjuntos de datos que una inserción recta.

BULK INSERT o BCP también se pueden usar para importar conjuntos de registros grandes.

Otra opción sería eliminar temporalmente todos los índices y restricciones en la tabla a la que está importando y agregarlos de nuevo una vez que se complete el proceso de importación. Una inserción recta que previamente falló podría trabajar en esos casos.

Si está tratando con tiempos de espera o problemas de bloqueo/bloqueo al ir directamente de una base de datos a otra, puede considerar pasar de una base de datos a TEMPDB y luego pasar de TEMPDB a la otra base de datos, ya que minimiza los efectos de los procesos de bloqueo y bloqueo en ambos lados. TempDB no bloqueará ni bloqueará la fuente y no detendrá el destino.

Esas son algunas opciones para probar.

-Eric Isaacs

Simple Insertar / Seleccionar el trabajo de sp es genial hasta que el recuento de filas supere 1 mil. He visto el archivo tempdb explotar tratando de insertar / seleccionar 20 mil + filas. La solución más simple es SSIS estableciendo el buffer de tamaño de fila de lote a 5000 y el buffer de tamaño de confirmación a 1000.

Seleccione * en new_items desde productDB.dbo.elementos

Eso es todo. Esta es la forma más eficiente de hacerlo.