En algunos casos, como DBA, se le solicita copiar el esquema y el contenido de tablas específicas desde una base de datos a otra en la misma instancia o en una instancia SQL diferente, como copiar tablas específicas de una base de datos de producción a una de DEV para propósitos de pruebas o solución de problemas.

SQL Server ofrece muchos métodos que pueden ser usados para realizar el proceso de copia de esquema y datos. Para ir a través de cada uno de estos métodos, consideraremos el siguiente escenario:

El anfitrión SQL Server: localhost. Ambas bases de datos están hospedadas en la misma instancia de SQL Server 2014.
La base de datos fuente: AdventureWorks2012.
La base de datos destino: SQLShackDemo.

Las tablas que serán copiadas desde la base de datos fuente a la de destino son: Department, Employee, EmployeeDepartmentHistory y EmployeePayHistory bajo el esquema HumanResources.

Métodos de Copiado de Tablas:

Usando una consulta SELECT INTO

Para copiar nuestras tablas desde la base de datos AdventureWorks2012 a SQLShackDemo, podemos usar la sentencia SQL SELECT INTO. Esta sentencia creará las tablas en la base de datos destino primero, luego copiará los datos a estas tablas. Si usted logra copiar los objetos de la base de datos como los índices y restricciones, usted necesita generar un script para eso individualmente, después de eso usted necesita aplicar los scripts a la base de datos destino.

En nuestro ejemplo, para copiar las tablas Department, Employee, EmployeeDepartmentHistory y EmployeePayHistory debajo del esquema HumanResources desde la base de datos AdventureWorks2012 a la base de datos SQLShackDemo, correremos el siguiente script:

Las columnas en las tablas destino son creadas en el orden especificado en la sentencia select. Todas estas columnas tienen el nombre exacto, el tipo de datos, la propiedad nullable, y los valores de columna como en la tabla fuente.

Si alguna de las tablas contiene una columna IDENTITY, la nueva columna en la tabla destino heredará la propiedad IDENTITY sin la necesidad de encender IDENTITY_INSERT.

Esto es válido en la mayoría de los casos, a menos que la sentencia SELECT contenga un JOIN, usted están usando UNION para unir múltiples sentencias SELECT, la columna IDENTITY es mencionada muchas veces en su sentencia select, o la fuente de esta columna IDENTITY es una fuente remota de datos.

Si cualquiera de estas condiciones mencionadas es verdadera, la columna será creada sin la propiedad NOT NULL en lugar de heredar la propiedad IDENTITY requerida.

Para sobreponerse a este problema de IDENTITY, usted puede usar la función IDENTITY SQL en la sentencia select para crear la columna IDENTITY.

Usando el asistente de Exportación/Importación de SQL Server

Otro método que puede ser usado para copiar tablas de la base de datos fuente a la de destino es el asistente de Exportación e Importación de SQL Server, el cual está disponible en SQL Server Management Studio. Usted tiene la opción de exportar desde la base de datos fuente o importar desde la de destino para transferir los datos.

1. Desde SQL Server Management Studio, haga clic derecho en la base de datos AdventureWorks2012 en el explorador de objetos, luego elija Tasks -> Export Data:

   

2. En la ventana Choose a Date Source del Asistente de Importación y Exportación, especifique el Nombre del Servidor, el método de Autenticación que será usado para conectarse al servidor fuente, y el nombre de la base de datos fuente, luego haga clic en Next.

   

3. En la ventana Choose a Destination del Asistente de Importación y Exportación, especifique el nombre del servidor destino, el método de Autenticación que será usado para conectarse al servidor destino y el nombre de la base de datos destino, luego haga clic en Next.

   

4. En Specify Table Copy or Query del Asistente de Exportación e Importación de SQL Server, elija Copy data from one or more tables or views. Haga clic en Next.

5. En Select Source Tables and Views del Asistente de Exportación e Importación de SQL Server, elija las tablas que serán copiadas desde la base de datos destino seleccionada a la de destino, luego haga clic en Next.

   

6. Para asegurarse de que las tablas serán creadas en la base de datos destino, haga clic en Edit Mappings y asegúrese de que Create destination table está seleccionado, y si alguna de sus tablas contiene la columna IDENTITY, asegúrese de seleccionar Enable Identity Insert, luego haga clic en OK. Si usted tiene más de una tabla a ser exportada a la base de datos destino, usted necesita hacer la revisión Edit Mapping tabla por tabla.

   

7. En Select Source Tables and Views, haga clic en Next.

8. En Run Package, haga clic en Next.

9. En complete the wizard, haga clic en Finish.

   

10. Una vez que la ejecución es completada exitosamente, usted puede ver los pasos que se siguieron para migrar las tablas y el número de registros transferidos. Revise los pasos y los mensajes, si no hay errores haga clic en Close.

    

Este método es una forma rápida de copiar tablas desde la base de datos fuente a la de destino si usted desea copiar las tablas sin preocuparse de las relaciones de las tablas y los órdenes.

Usando este método, los índices de las tablas y claves no serán transferidos. Si usted está interesado en copiarlos, usted necesita generar scripts para estos objetos de la base de datos.

Si estas son Claves Foráneas conectando estas tablas, usted necesita exportar los datos en el orden correcto, de otra manera, el asistente de exportación fallará.

Usando Generate Scripts

SQL Server provee otra manera de generar un script para las bases de datos SQL Server con sus objetos y datos. Este script puede ser usado para copiar el esquema de las tablas y datos desde la base de datos fuente a la de destino en nuestro caso.

1. Usando SQL Server Management Studio, haga clic derecho en la base de datos fuente desde el explorador de objetos, luego, elija Generate Scripts desde Tasks.

   

2. En la ventana Choose objects, elija Select Specific Database Objects para especificar las tablas para las que generará el script, luego elija cada tabla. Haga clic en Next.

   

3. En la ventana Set Scripting Options, especifique la ruta donde grabará el archivo de script generado, y haga clic en Advanced.

   

4. Desde la ventana Advanced Scripting Options, especifique Schema and Data como Types of Data to Script. Usted puede decidir desde aquí si desea codificar los índices y claves en sus tablas. Haga clic en OK.

5. Volviendo a la ventana Advanced Scripting Options, haga clic en Next.

   

6. Revise la ventana Summary y haga clic en Next.

   

7. Usted puede monitorear el progreso desde la ventana Save or Publish Scripts. Si no hay error, haga clic en Finish y usted encontrará el archivo de script en la ruta especificada.

   

El método de SQL Scripting es útil para generar un solo script para el esquema de las tablas y los datos, incluyendo los índices y las claves. Pero de nuevo, este método no genera el script de creación de las tablas en el orden correcto si hay relaciones entre las tablas.

Usando la combinación de ApexSQL Diff y ApexSQL Data Diff

ApexSQL Diff es una herramienta SQL útil que puede ser usada para encontrar las diferencias entre las bases de datos desde el lado del esquema y generar un script de sincronización para crear estas tablas en la base de datos destino en el orden correcto.

ApexSQL Data Diff también es otra herramienta SQL que puede ser usada para encontrar las diferencias entre las bases de datos desde el lado de los datos y generar un script de sincronización para insertar los datos en las tablas de la base de datos destino, tomando en consideración las columnas IDENTITY.

Podemos tomar beneficios de estas dos herramientas juntas para generar los scripts requeridos para crear las tablas a ser copiadas con sus objetos, y copiar los datos desde las tablas fuente a las de destino.

1. Inicie la herramienta ApexSQL Diff.

2. Desde el panel Data sources, especifique el nombre del servidor fuente, el nombre de la base de datos y el método de Autenticación usado para conectarse al servidor fuente. Usted necesita especificar un nombre de usuario y una contraseña válidos si elije SQL Server Authentication.

   También especifique el nombre del servidor destino, el nombre de la base de datos destino y el método de Autenticación usado para conectarse al servidor destino. Usted necesita especificar un nombre de usuario y una contraseña válidos si elije SQL Server Authentication.

   Usted puede cambiar el rol de cada servidor especificado como la fuente o el destino haciendo clic en Reverse. Luego, haga clic en Connect.

3. Haga clic en Compare.

   

   Una nueva ventana será mostrada, que contiene los objetos que son comunes entre las dos bases de datos y los objetos existen en una de las bases de datos pero no existen en la segunda.

   En nuestro ejemplo, estamos interesados en los objetos que existen en la base de datos AdventureWorks2012 y no existen en la base de datos SQLShackDemo, donde podemos encontrar las tablas a ser copiadas.

   

4. Expanda los objetos que existen en la base de datos AdventureWorks2014 solamente, y elija las tablas que copiará. Haga clic en Synchronize.

   

5. En Synchronization Direction de la ventana Synchronization wizard, haga clic en Next si no desea cambiar la dirección de Sincronización.

   

6. En Dependencies de la ventana Synchronization wizard, deseleccione Include dependent database objects si no desea codificar las tablas relacionadas. Haga clic en Next.

   

7. En Output options de la ventana Synchronization wizard, elija Create a synchronization script si desea generar el script y correrlo manualmente, o Synchronize now para dejar a la herramienta correr el script en el servidor destino directamente. Haga clic en Next.

   

8. En Summary and warning de la ventana Synchronization wizard, revise las Acciones y haga clic en Create Script.

   

   Ahora el script para crear las tablas de la base de datos con sus objetos es generado con el orden correcto. Usted puede simplemente correr este script en la base de datos destino para crear las tablas.

   

9. Inicie la herramienta ApexSQL Data Diff.

10. Desde el panel Data sources, especifique el nombre del servidor fuente, el nombre de la base de datos fuente y el método de Autenticación usado para conectarse al servidor fuente. Usted necesita especificar un nombre de usuario y una contraseña válidos si elije la Autenticación de SQL Server.

    También especifique el nombre del servidor destino, el nombre de la base de datos de destino y el método de Autenticación usado para conectarse al servidor destino. Usted necesita especificar un nombre de usuario y una contraseña válidos si elije Autenticación de SQL Server.

    Usted puede cambiar el rol de cada servidor especificado como la fuente o destino haciendo clic en Reverse. Luego, haga clic en Connect.

11. Haga clic en Compare.

    

    Una nueva ventana será mostrada, que contiene las diferencias de datos entre las tablas en las bases de datos fuente y destino en adición a los datos idénticos y no comparables.

    Aquí estamos interesados en las diferencias entre las tablas en las bases de datos de fuente y destino. Como en los pasos previos, creamos estas tablas pero aún están vacías.

12. Desde la cuadrícula de resultados de diferencias, elija las tablas de las que necesita copiar los datos a la base de datos destino, luego haga clic en Synchronize.

    

13. En Synchronization Direction de la ventana Synchronization wizard, haga clic en Next si no desea cambiar la dirección de Sincronización.

    

14. En Output options de la ventana Synchronization wizard, elija Create a synchronization script si quiere generar el script y correrlo manualmente, o elija Synchronize now para dejar a la herramienta correr el script en el servidor destino. Haga clic en Next.

    

15. En Summary and Warnings de la ventana Synchronization wizard, revise las acciones y haga clic en Create Script.

    

Ahora el script para insertar las tablas de la base de datos, manejando la inserción IDENTITY, es generado. Usted puede simplemente correr este script en la base de datos destino para insertar los datos de las tablas.

La combinación de las herramientas ApexSQL Diff y ApexSQL Data Diff es útil para copiar tablas de bases de datos SQL generando un script completo para el esquema de estas tablas con çindices y claves y en el orden correcto, y el manejo de los datos de las tablas con la inserción de la columna IDENTITY.

Usando ApexSQL Script:

Otra herramienta útil provista por ApexSQL que puede ser usada para copiar tablas, datos y esquema desde la base de datos fuente a la de destino es la herramienta ApexSQL Script. Esta linda herramienta creará un script para el esquema de las tablas de la base de datos y los datos con índices y claves de estas tablas manejando la inserción de la columna IDENTITY.

1. Inicie la herramienta de ApexSQL Script.

2. Desde el panel Select Databases, especifique el nombre del servidor fuente y el método de autenticación usado para conectarse al servidor fuente. Usted necesita especificar un nombre de usuario y una contraseña válidos si elije la Autenticación de SQL Server. Haga clic en Connect.

3. Una lista de todas las bases de datos hospedadas en su servidor será visto en la cuadrícula de resultados derecha. Elija el nombre de la base de datos fuente, que es AdventureWorks2012 en nuestro ejemplo.

   

4. Desde el panel Data, seleccione Set IDENTITY_INSERT ON desde la sección General e INSERT desde la sección Script row as. Haga clic en Open.

   

5. Desde la ventana mostrada, elija las tablas que copiará a la base de datos destino y haga clic en Script:

   

6. Elija Structure and Data como modo de codificación y T-SQL como tipo de salida desde el asistente de Script. Haga clic en Next.

   

7. En la ventana Dependencies, haga clic en Next.

8. En la ventana Output file, desde la lista desplegable Action, elija create and write to file. Luego usted tiene la opción de grabar el script a una ruta específica o abrirlo en un editor. Elija Open script in editor y luego haga clic en Create.

   

Ahora, un sólo script completo es generado. Sólo córralo en la base de datos destino y las tablas serán copiadas completamente. Usted puede ir a través del script para revisar los scripts de creación de tablas en el orden correcto, como sigue:

También, usted puede encontrar las sentencias INSERT para los datos de las tablas, como sigue:

ApexSQL Script es una herramienta muy útil que puede ser usada para copiar tablas de bases de datos de una base de datos fuente a una de destino sin ningún esfuerzo de su parte para manejar el orden de creación de las tablas. Tampoco ninguna acción es requerida de su lado para manejar la inserción IDENTITY.

Conclusión:

Como puede ver, múltiples métodos pueden ser usados para copiar tablas desde la base de datos fuente a la de destino, incluyendo esquema y datos. La mayoría de estas herramientas requieren un gran esfuerzo de su lado para copiar los objetos de las tablas como índices y claves. También, estas herramientas no manejarán el orden de creación para estas tablas si hay relaciones entre ellas. Desde el lado de los datos, la mayoría de estas herramientas necesitan pasos extra de su lado para manejar la inserción IDENTITY. Usando la combinación de ApexSQL Diff y ApexSQL Data Diff, o usando ApexSQL Script, con unos pocos pasos simples usted tendrá un script completo para el esquema de las tablas y los datos sin esfuerzos extra de su parte para manejar todo. Sólo corra estos scripts en la base de datos destino y disfrute el resultado.

Enlaces útiles:

• Revisar IDENTITY
• Leer más acerca de ApexSQL Diff
• Revise también ApexSQL Data Diff

• Acerca de
• Últimas entradas

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Últimas entradas de Ahmad Yaseen (ver todo)

• Restricciones en SQL Server: SQL NOT NULL, UNIQUE y SQL PRIMARY KEY - December 16, 2019
• Operaciones de copia de seguridad, truncamiento y reducción de registros de transacciones de SQL Server - November 4, 2019
• Qué elegir al asignar valores a las variables de SQL Server: sentencias SET vs SELECT T-SQL - November 4, 2019