sql-server - temporales - variables tipo tabla sql server




¿Cuál es la mejor manera de generar automáticamente sentencias INSERT para una tabla de SQL Server? (13)

Estamos escribiendo una nueva aplicación, y mientras probamos, necesitaremos un montón de datos ficticios. He agregado esos datos utilizando MS Access para volcar archivos de Excel en las tablas relevantes.

De vez en cuando, queremos "actualizar" las tablas relevantes, lo que significa eliminarlas todas, volver a crearlas y ejecutar una consulta de apéndice de MS Access guardada.

La primera parte (eliminación y recreación) es un script de SQL sencillo, pero la última parte me hace temblar. Quiero un solo script de configuración que tenga un montón de INSERTs para regenerar los datos ficticios.

Tengo los datos en las tablas ahora. ¿Cuál es la mejor manera de generar automáticamente una gran lista de declaraciones INSERT de ese conjunto de datos?

La única manera en que puedo pensar en hacerlo es guardar la tabla en una hoja de Excel y luego escribir una fórmula de Excel para crear un INSERT para cada fila, lo que seguramente no es la mejor manera.

Estoy usando 2008 Management Studio para conectarme a una base de datos de SQL Server 2005.


¿Tienes datos en una base de datos de producción todavía? Si es así, puede configurar una actualización periódica de los datos a través de DTS. Hacemos lo nuestro semanalmente los fines de semana y es muy bueno tener datos reales y limpios cada semana para nuestras pruebas.

Si aún no tiene producción, debe crear una base de datos que es la que ellos desean que desee (nueva). Luego, duplique esa base de datos y use esa base de datos recién creada como su entorno de prueba. Cuando desee la versión limpia, simplemente duplique su versión limpia nuevamente y Bob es su tío .


¿por qué no hacer una copia de seguridad de los datos antes de trabajar con ellos y luego restaurarlos cuando desee que se actualicen?

Si debe generar inserciones, intente: http://vyaskn.tripod.com/code.htm#inserts


El primer enlace a sp_generate_inserts es bastante bueno, aquí hay una versión realmente simple:

DECLARE @Fields VARCHAR(max); SET @Fields = '[QueueName], [iSort]' -- your fields, keep []
DECLARE @Table  VARCHAR(max); SET @Table  = 'Queues'               -- your table

DECLARE @SQL    VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '', ''INSERT INTO ' + @Table + '(' + @Fields + ')'') + CHAR(13) + CHAR(10) + 
 ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Fields, ',', ' + '', '' + '), '[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''') +' FROM ' + @Table + '
PRINT @S'

EXEC (@SQL)

En mi sistema, obtengo este resultado:

INSERT INTO Queues([QueueName], [iSort])
SELECT 'WD: Auto Capture', '10' UNION 
SELECT 'Car/Lar', '11' UNION 
SELECT 'Scan Line', '21' UNION 
SELECT 'OCR', '22' UNION 
SELECT 'Dynamic Template', '23' UNION 
SELECT 'Fix MICR', '41' UNION 
SELECT 'Fix MICR (Supervisor)', '42' UNION 
SELECT 'Foreign MICR', '43' UNION 
...

Esto se puede hacer usando Visual Studio también (al menos en la versión 2013 en adelante).

En VS 2013 también es posible filtrar la lista de filas en las que se basa la declaración de inserciones, esto no es posible en SSMS como lo sé.

Realice los siguientes pasos:

  • Abra la ventana "Explorador de objetos de SQL Server" (menú: / Ver / Explorador de objetos de SQL Server)
  • Abrir / ampliar la base de datos y sus tablas.
  • Haga clic derecho en la tabla y elija "Ver datos" del menú contextual
  • Esto mostrará los datos en el área principal.
  • Paso opcional: haga clic en el ícono de filtro "Ordenar y filtrar el conjunto de datos" (el cuarto ícono de la izquierda en la fila sobre el resultado) y aplique un filtro a una o más columnas
  • Haga clic en los íconos "Script" o "Script to File" (los íconos a la derecha de la fila superior, se ven como pequeñas hojas de papel)

Esto creará las instrucciones de inserción (condicionales) para la tabla seleccionada en la ventana o archivo activo.


Los botones "Filtro" y "Script" de Visual Studio 2013 :


Mi contribución al problema, un generador de secuencias de comandos Powershell INSERT que le permite realizar secuencias de comandos de varias tablas sin tener que usar la incómoda GUI de SSMS. Excelente para datos de "semilla" que persisten rápidamente en el control de fuente.

  1. Guarde la siguiente secuencia de comandos como "filename.ps1".
  2. Haga sus propias modificaciones a las áreas bajo "PERSONALIZAR ME".
  3. Puede agregar la lista de tablas al script en cualquier orden.
  4. Puede abrir la secuencia de comandos en Powershell ISE y presionar el botón Reproducir, o simplemente ejecutar la secuencia de comandos en el símbolo del sistema de Powershell.

De forma predeterminada, el script INSERT generado será "SeedData.sql" en la misma carpeta que el script.

Necesitará los conjuntos de objetos de administración de SQL Server instalados, que deberían estar allí si tiene instalado SSMS.

Add-Type -AssemblyName ("Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
Add-Type -AssemblyName ("Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")



#CUSTOMIZE ME
$outputFile = ".\SeedData.sql"
$connectionString = "Data Source=.;Initial Catalog=mydb;Integrated Security=True;"



$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$srv = new-object Microsoft.SqlServer.Management.Smo.Server($conn)
$db = $srv.Databases[$srv.ConnectionContext.DatabaseName]
$scr = New-Object Microsoft.SqlServer.Management.Smo.Scripter $srv
$scr.Options.FileName = $outputFile
$scr.Options.AppendToFile = $false
$scr.Options.ScriptSchema = $false
$scr.Options.ScriptData = $true
$scr.Options.NoCommandTerminator = $true

$tables = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection



#CUSTOMIZE ME
$tables.Add($db.Tables["Category"].Urn)
$tables.Add($db.Tables["Product"].Urn)
$tables.Add($db.Tables["Vendor"].Urn)



[void]$scr.EnumScript($tables)

$sqlConnection.Close()

Microsoft debería anunciar esta funcionalidad de SSMS 2008. La función que está buscando está incorporada en la utilidad Generar Script , pero la funcionalidad está desactivada de forma predeterminada y debe estar habilitada cuando se realiza una secuencia de comandos de una tabla.

Esta es una ejecución rápida para generar las INSERT para todos los datos en su tabla, sin usar scripts ni complementos para SQL Management Studio 2008:

  1. Haga clic con el botón derecho en la base de datos y vaya a Tareas > Generar scripts .
  2. Seleccione las tablas (u objetos) contra las que desea generar el script.
  3. Vaya a la pestaña Configurar opciones de secuencias de comandos y haga clic en el botón Avanzado .
  4. En la categoría General , vaya a Tipo de datos a script.
  5. Hay 3 opciones: Solo esquema , Solo datos , y Esquema y datos . Seleccione la opción apropiada y haga clic en Aceptar .

A continuación, obtendrá la CREATE TABLE y todas las INSERT para los datos directamente de SSMS.


No uses inserciones, usa BCP




Si necesita un acceso programático, puede utilizar un procedimiento almacenado de código abierto `GenerateInsert.

Generador de declaraciones INSERT

Solo como un ejemplo simple y rápido, para generar instrucciones INSERT para una tabla AdventureWorks.Person.AddressType ejecute las siguientes instrucciones:

USE [AdventureWorks];
GO
EXECUTE dbo.GenerateInsert @ObjectName = N'Person.AddressType';

Esto generará el siguiente script:

SET NOCOUNT ON
SET IDENTITY_INSERT Person.AddressType ON
INSERT INTO Person.AddressType
([AddressTypeID],[Name],[rowguid],[ModifiedDate])
VALUES
 (1,N'Billing','B84F78B1-4EFE-4A0E-8CB7-70E9F112F886',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(2,N'Home','41BC2FF6-F0FC-475F-8EB9-CEC0805AA0F2',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(3,N'Main Office','8EEEC28C-07A2-4FB9-AD0A-42D4A0BBC575',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(4,N'Primary','24CB3088-4345-47C4-86C5-17B535133D1E',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(5,N'Shipping','B29DA3F8-19A3-47DA-9DAA-15C84F4A83A5',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
,(6,N'Archive','A67F238A-5BA2-444B-966C-0467ED9C427F',CONVERT(datetime,'2002-06-01 00:00:00.000',121))
SET IDENTITY_INSERT Person.AddressType OFF

Utilicé este script que he puesto en mi blog ( Cómo generar procedimientos de declaración de inserción en el servidor SQL ).

Hasta ahora me ha funcionado, aunque pueden ser errores que aún no he descubierto.


Utilizamos este procedimiento almacenado: le permite apuntar a tablas específicas y usar cláusulas donde. Puedes encontrar el texto here .

Por ejemplo, te permite hacer esto:

EXEC sp_generate_inserts 'titles'

GenerateData es una herramienta increíble para esto. También es muy fácil hacerle ajustes porque el código fuente está disponible para ti. Algunas características agradables:

  • Generador de nombres para los nombres y lugares de los pueblos.
  • Posibilidad de guardar el perfil de Generación (después de descargarlo y configurarlo localmente)
  • Posibilidad de personalizar y manipular la generación mediante scripts.
  • Muchas salidas diferentes (CSV, Javascript, JSON, etc.) para los datos (en caso de que necesite probar el conjunto en diferentes entornos y desee omitir el acceso a la base de datos)
  • Gratis Pero considera donar si encuentras el software útil :).





code-generation