Script para generar automáticamente inserts de los datos de una tabla SQL Server

Hay muchas herramientas, utilidades y asistentes para realizar extracciones y migraciones de datos de una tabla de SQL Server a una tabla de otra base de datos, pero no siempre disponemos de esas herramientas, o de permisos suficientes para utilizarlas y entonces, la mejor manera de hacerlo puede ser generando las sentencias de insert necesarias, que simplemente habrá que ejecutar en la base de datos destino.

Generar sentencias de INSERT con SQL

Para generar estas sentencias de insert, que nadie quiere picar 'a mano', una opción interesante es utilizar un script de SQL dinámico que simplemente indicándole con un parámetro cuál es la tabla de la que queremos migrar datos, nos genere las sentencias de inserción de todos los datos de todos los campos de esa tabla.

Lo mejor es que los nombres de los campos se obtienen del diccionario de metadatos de SQL Server, por lo que no hay que introducir ni un sólo nombre de campo, el mismo script ya los saca todos.

Script SQL para generar INSERTS de exportación de datos

Este es el pequeño script SQL que a mi me ha sacado de más de un apuro. Se puede utilizar con SQL Server Management Studio, por ejemplo, y sólo hay que abrir una conexión a la base de datos de origen, inicializar la variable @Table con el nombre de la tabla de la que se quieran obtener los inserts de sus datos, y ejecutar.

El script obtiene de syscolumns los nombres de las columnas de la tabla, y con ellos genera un SQL dinámico que al ejecutarlo devuelve una sentencia de insert por cada registro de la tabla.

Después sólo hay que copiar las sentencias desde el mismo SSMS, o exportarlas a un fichero si la tabla es muy voluminosa, conectar a la base de datos destino, y ejecutar las sentencias desde una hoja de consultas, o desde el fichero si se han exportado.

Se puede modificar fácilmente anadiéndole un where y las condiciones que se quiera si sólo se desea obtener los inserts de un subconjunto de los datos de la tabla.

También, en las selects que sacan los nombres de los campos, he dejado una condición AND name <> 'me' para poder cambiar 'me' por nombres de campos que se quieran excluir en la generación de las sentencias de insert.

Puede ser útil, por ejemplo, si la tabla contiene algún campo de tipo identity y la tabla de destino ya contiene datos. Como en este caso seguramente nos interesaría que los registros que insertemos continúen la numeración del identity, este campo habría que excluirlo del insert con " 'AND name <> 'CampoIdentity' ".

Script de generación de inserts

-----------------------------------------------------------------------
-- Generation of dynamic inserts for all the columns of table @Table --
-----------------------------------------------------------------------
DECLARE @Table VARCHAR(max)='MySchema.MyTable'

DECLARE @ColumnsList VARCHAR(max)=''
DECLARE @ColumnsList4Values VARCHAR(max)=''
DECLARE @SQL as VARCHAR(max)=''

SELECT @ColumnsList= STUFF ((
    SELECT ', [' + name + ']'
    FROM syscolumns
    WHERE id = OBJECT_ID(''+@Table+'') AND name <> 'me'
    FOR XML PATH('')), 1, 1, '')
-- print @ColumnsList

SELECT @ColumnsList4Values= STUFF ((
    SELECT ','''''' + ISNULL(TRY_CAST([' + name + '] AS varchar),'''') + '''''' '
    FROM syscolumns
    WHERE id = OBJECT_ID(''+@Table+'') AND name <> 'me'
    FOR XML PATH('')), 1, 1, '')
-- print @ColumnsList4Values

-- Create final SQL query that generates inserts ----------
SELECT @SQL=
        'SELECT ''INSERT INTO ' + @table + ' ('+
        @ColumnsList +
        ') ' +
        'VALUES (' +
        @ColumnsList4Values+
        ')'' Inserts FROM ' + @table 
-- print @SQL

select @SQL Query -- Query2 Insert values
exec (@SQL)
-- END ---------------------------------------------------

Volviendo al tema Identity, como el script genera por defecto los inserts para todos los campos de la tabla, si esta tabla tiene algún campo de tipo identity, y queremos conservar exactamente los mismos id's en este campo de la tabla destino, hay que acordarse de permitir la inserción en campos identity con SET IDENTITY INSERT ON antes de hacer los inserts, y dejarla como estaba cuando terminen.

Ya que estamos con SQL dinámico podemos generar las sentencias de activación y desactivación de identity así:

-- If exists field identity ------------------------------
DECLARE @Table VARCHAR(max)='MySchema.MyTable'

SELECT 'SET IDENTITY_INSERT '+ @Table + ' ON;'
SELECT 'SET IDENTITY_INSERT '+ @Table + ' OFF;'
-- END ---------------------------------------------------

Posibles mejoras del script

Por último comentar que el Script funciona bien con los tipos de datos más habituales, pero es un script sencillo y muy mejorable, y puede generar sentencias incorrectas según los tipos. Si la tabla contiene algún campo de tipo UniqueIdentifier, por ejemplo, se producen errores de conversión.

Si alguien se anima a investigarlo y mejorar el script, cualquier aportación será bienvenida!