tipo temporales tablas tabla las funciones eliminar donde dinamica crear almacenan sql-server ssms code-generation

sql-server - funciones - tablas temporales sql server 2016



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

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

Como lo mencionó @Mike Ritacco pero actualizado para SSMS 2008 R2

  1. Haga clic derecho en el nombre de la base de datos
  2. Elija Tareas> Generar guiones
  3. Dependiendo de su configuración, la página de introducción puede mostrarse o no
  4. Seleccione ''Seleccionar objetos específicos de la base de datos'',
  5. Expande la vista de árbol y revisa las tablas relevantes.
  6. Haga clic en Siguiente
  7. Haga clic en Avanzado
  8. En la sección General, elija la opción apropiada para ''Tipos de datos a secuencia de comandos''
  9. Completar el asistente

A continuación, obtendrá todas las declaraciones INSERT para los datos directamente de SSMS.

EDITAR 2016-10-25 SQL Server 2016 / SSMS 13.0.15900.1

  1. Haga clic derecho en el nombre de la base de datos

  2. Elija Tareas> Generar guiones

  3. Dependiendo de su configuración, la página de introducción puede mostrarse o no

  4. Seleccione ''Seleccionar objetos específicos de la base de datos'',

  5. Expande la vista de árbol y revisa las tablas relevantes.

  6. Haga clic en Siguiente

  7. Haga clic en Avanzado

  8. En la sección General, elija la opción apropiada para ''Tipos de datos a secuencia de comandos''

  9. Haga clic en Aceptar

  10. Elija si desea que la salida vaya a una nueva consulta, al portapapeles o a un archivo

  11. Haga clic en Siguiente dos veces

  12. Su guión está preparado de acuerdo con la configuración que seleccionó anteriormente

  13. Haga clic en Finalizar

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 :

Estoy usando la versión 10.0.5500.0 de SSMS 2008. En esta versión, como parte del asistente Generar scripts, en lugar de un botón Avanzado, se muestra la siguiente pantalla. En este caso, solo quería insertar los datos y no crear declaraciones, así que tuve que cambiar las dos propiedades encerradas en un círculo

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 estoy seguro, si entiendo su pregunta correctamente.

Si tiene datos en MS-Access, que desea trasladarlos a SQL Server, puede usar DTS.
Y supongo que podría usar el generador de perfiles SQL para ver todas las declaraciones INSERT.

No uses inserciones, usa BCP

Puede usar el paquete de herramientas SSMS (disponible para SQL Server 2005 y 2008). Viene con una característica para generar instrucciones de inserción.

http://www.ssmstoolspack.com/

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

También he investigado mucho sobre esto, pero no pude encontrar la solución concreta para esto. Actualmente, el enfoque que sigo es copiar los contenidos en Excel de SQL Server Managment studio y luego importar los datos en Oracle-TOAD y luego generar las declaraciones de inserción

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

Yo uso sqlite para hacer esto. Me parece muy, muy útil para crear bases de datos de prueba / scratch.

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql

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 :).