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.

Script para generación de SQL Inserts dinámicos
Resultados del Script para generar Inserts

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.

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 ---------------------------------------------------

El script genera los inserts para todos los campos de la tabla, por lo que si esta tabla tiene algún campo de tipo identity, 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 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!