servidor - Qual é a melhor maneira de gerar automaticamente instruções INSERT para uma tabela do SQL Server?



insert com where (13)

A Microsoft deve anunciar essa funcionalidade do SSMS 2008. O recurso que você está procurando está embutido no utilitário Gerar Script , mas a funcionalidade está desativada por padrão e deve ser ativada ao criar scripts de uma tabela.

Essa é uma execução rápida para gerar as instruções INSERT para todos os dados da tabela, sem usar scripts ou suplementos no SQL Management Studio 2008:

  1. Clique com o botão direito do mouse no banco de dados e vá para Tarefas > Gerar Scripts .
  2. Selecione as tabelas (ou objetos) com os quais você deseja gerar o script.
  3. Vá para a guia Definir opções de script e clique no botão Avançado .
  4. Na categoria Geral , vá para Tipo de dados para script
  5. Existem 3 opções: Somente Esquema , Somente Dados e Esquema e Dados . Selecione a opção apropriada e clique em OK .

Você então obterá a instrução CREATE TABLE e todas as instruções INSERT para os dados diretamente do SSMS.

Estamos escrevendo um novo aplicativo e, durante os testes, precisaremos de vários dados fictícios. Eu adicionei esses dados usando o MS Access para despejar arquivos do Excel nas tabelas relevantes.

De vez em quando, queremos "atualizar" as tabelas relevantes, o que significa descartá-las, recriá-las e executar uma consulta anexada do MS Access salva.

A primeira parte (soltar e recriar) é um script sql fácil, mas a última parte me faz estremecer. Eu quero um script de instalação único que tenha um monte de INSERTs para regenerar os dados fictícios.

Eu tenho os dados nas tabelas agora. Qual é a melhor maneira de gerar automaticamente uma grande lista de instruções INSERT desse conjunto de dados?

A única maneira que posso pensar em fazer isso é salvar a tabela em uma planilha de excel e, em seguida, escrever uma fórmula de excel para criar um INSERT para cada linha, o que certamente não é o melhor caminho.

Eu estou usando o 2008 Management Studio para se conectar a um banco de dados do SQL Server 2005.


Answer #1

Como mencionado por @Mike Ritacco mas atualizado para o SSMS 2008 R2

  1. Clique com o botão direito no nome do banco de dados
  2. Escolha Tarefas> Gerar scripts
  3. Dependendo das suas configurações, a página de introdução pode mostrar ou não
  4. Escolha 'Selecionar objetos de banco de dados específicos',
  5. Expanda a exibição em árvore e verifique as tabelas relevantes
  6. Clique em Avançar
  7. Clique em Avançado
  8. Na seção Geral, escolha a opção apropriada para 'Tipos de dados para script'
  9. Complete o assistente

Você então obterá todas as instruções INSERT para os dados diretamente do SSMS.

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

  1. Clique com o botão direito no nome do banco de dados

  2. Escolha Tarefas> Gerar scripts

  3. Dependendo das suas configurações, a página de introdução pode mostrar ou não

  4. Escolha 'Selecionar objetos de banco de dados específicos',

  5. Expanda a exibição em árvore e verifique as tabelas relevantes

  6. Clique em Avançar

  7. Clique em Avançado

  8. Na seção Geral, escolha a opção apropriada para 'Tipos de dados para script'

  9. Clique OK

  10. Escolha se você deseja que a saída vá para uma nova consulta, a área de transferência ou um arquivo

  11. Clique em Avançar duas vezes

  12. Seu script é preparado de acordo com as configurações escolhidas acima

  13. Clique em Finish


Answer #2

Eu também pesquisei muito sobre isso, mas não consegui a solução concreta para isso. Atualmente, a abordagem que eu sigo é copiar o conteúdo em excel do SQL Server Managment studio e depois importar os dados para o Oracle-TOAD e então gerar as instruções insert


Answer #3

Eu usei esse script que eu coloquei no meu blog ( Como gerar procedimentos de instrução Insert no sql server ).

Até agora funcionou para mim, embora possam ser erros que ainda não descobri.


Answer #4

Isso pode ser feito usando o Visual Studio também (pelo menos na versão 2013 em diante).

No VS 2013, também é possível filtrar a lista de linhas na qual a instrução de inserções é baseada, isso é algo que não é possível no SSMS, como eu sei.

Execute os seguintes passos:

  • Abra a janela "Explorador de Objetos do SQL Server" (menu: / View / SQL Server Object Explorer)
  • Abra / expanda o banco de dados e suas tabelas
  • Clique com o botão direito do mouse na tabela e escolha "Visualizar dados" no menu de contexto
  • Isso exibirá os dados na área principal
  • Etapa opcional: clique no ícone de filtro "Classificar e filtrar o conjunto de dados" (o quarto ícone da esquerda na linha acima do resultado) e aplique um filtro a uma ou mais colunas
  • Clique nos ícones "Script" ou "Script para Arquivo" (os ícones à direita da linha superior, eles se parecem com pequenas folhas de papel)

Isso criará as instruções de inserção (condicionais) da tabela selecionada para a janela ou arquivo ativo.


Os botões "Filtro" e "Script" do Visual Studio 2013 :


Answer #5

Minha contribuição para o problema, um gerador de script Powershell INSERT que permite que você faça o script de várias tabelas sem precisar usar a complicada GUI do SSMS. Ótimo para dados de "semente" que persistem rapidamente no controle de origem.

  1. Salve o script abaixo como "filename.ps1".
  2. Faça suas próprias modificações nas áreas em "PERSONALIZAR ME".
  3. Você pode adicionar a lista de tabelas ao script em qualquer ordem.
  4. Você pode abrir o script no Powershell ISE e clicar no botão Play ou simplesmente executar o script no prompt de comando do Powershell.

Por padrão, o script INSERT gerado será "SeedData.sql" na mesma pasta do script.

Você precisará dos assemblies de Objetos de Gerenciamento do SQL Server instalados, que devem estar lá se você tiver o SSMS instalado.

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

Answer #6

Não use inserções, use BCP


Answer #7

O primeiro link para sp_generate_inserts é bem legal, aqui está uma versão bem simples:

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)

No meu sistema, recebo 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 
...


Answer #9

Se você precisar de um acesso programático, então você pode usar um procedimento armazenado de fonte aberta `GenerateInsert.

Gerador de declaração (ões) INSERT

Apenas como um exemplo simples e rápido, para gerar instruções INSERT para uma tabela AdventureWorks.Person.AddressType execute as seguintes instruções:

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

Isso gerará o seguinte 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

Answer #10

Usamos este procedimento armazenado - ele permite que você segmente tabelas específicas e use cláusulas where. Você pode encontrar o texto here .

Por exemplo, permite fazer isso:

EXEC sp_generate_inserts 'titles'

Answer #11

Você pode usar o SSMS Tools Pack (disponível para o SQL Server 2005 e 2008). Ele vem com um recurso para gerar instruções de inserção.

http://www.ssmstoolspack.com/


Answer #12

GenerateData é uma ferramenta incrível para isso. Também é muito fácil fazer ajustes porque o código-fonte está disponível para você. Alguns recursos interessantes:

  • Gerador de nomes para nomes e lugares de pessoas
  • Capacidade de salvar o perfil de Geração (depois que ele é baixado e configurado localmente)
  • Capacidade de personalizar e manipular a geração através de scripts
  • Muitas saídas diferentes (CSV, Javascript, JSON, etc.) para os dados (no caso de você precisar testar o conjunto em diferentes ambientes e desejar ignorar o acesso ao banco de dados)
  • Grátis . Mas considere doar se você achar o software útil :).





code-generation