Conversion des résultats de la sélection en script d’insertion – SQL Server

J’ai SQL Server 2008 , SQL Server Management Studio.

Je dois sélectionner les données de la Table1 dans la base de Table1 . Ensuite, je dois modifier certaines valeurs dans les résultats et insert values into Table1 dans database2.

Ou laissez-moi le dire autrement.

Comment puis-je convertir les données d’une table en insert script ?

SSMS Toolpack (qui est GRATUIT comme dans la bière) possède une grande variété de fonctionnalités – y compris la génération d’instructions INSERT à partir de tables.

Mise à jour: pour SQL Server Management Studio 2012 (et versions ultérieures), SSMS Toolpack n’est plus gratuit, mais nécessite des frais de licence modestes.

Voici une autre méthode, qui peut être plus facile que d’installer des plugins ou des outils externes dans certaines situations:

  • Faites une select [whatever you need] INTO temp.table_name from [... etc ...] .
  • Cliquez avec le bouton droit sur la firebase database dans l’Explorateur d’objects => Tâches => Générer des scripts
  • Sélectionnez temp.table_name dans l’écran “Choose Objects”, cliquez sur Next.
  • Dans l’écran “Spécifier comment les scripts doivent être enregistrés”:
    • Cliquez sur Avancé, recherchez la propriété “Types de données dans le script”, sélectionnez “Données uniquement”, fermez les propriétés avancées.
    • Sélectionnez “Enregistrer dans la nouvelle fenêtre de requête” (sauf si vous avez des milliers d’enregistrements).
  • Cliquez sur Suivant, attendez que le travail soit terminé, observez les instructions INSERT résultantes qui apparaissent dans une nouvelle fenêtre de requête.
  • Utilisez Rechercher et remplacer pour remplacer tous [temp.table_name] par [your_table_name] .
  • drop table [temp.table_name] .

Méthode native :

par exemple si vous avez une table

 Users(Id, name) 

Tu peux le faire:

 select 'insert into Table values(Id=' + Id + ', name=' + name + ')' from Users 

Dans SSMS:

  • Faites un clic droit sur la firebase database> Tâches> Générer des scripts

  • Prochain

  • Sélectionnez “Sélectionner des objects de firebase database spécifiques” et vérifiez la table que vous souhaitez créer un script, puis cliquez sur Suivant.

  • Cliquez sur Advanced > dans la liste des options, faites défiler vers le bas et recherchez les “Types de données dans le script” et remplacez-le par “Données uniquement”> OK

  • Sélectionnez “Enregistrer dans la nouvelle fenêtre de requête”> Suivant> Suivant> Terminer

Les 180 lignes sont désormais rédigées en 180 instructions!

En utilisant Visual Studio, procédez comme suit

Créer un projet de type SQL Server -> Projet de firebase database SQL Server

ouvrir l’explorateur de serveur sql CTL- \, CTL-S

Ajoutez un serveur SQL en cliquant avec le bouton droit sur l’icône SQL SERVER. Selcet AJOUTEZ UN NOUVEAU SERVEUR

naviguez jusqu’à la table qui vous intéresse

clic droit -> AFFICHER LES DONNÉES

Cliquez sur la cellule en haut à gauche pour surligner tout (ctl-A ne semble pas fonctionner)

Clic droit -> SCript

C’est fabuleux. J’ai essayé tout ce qui est indiqué ci-dessus au fil des ans. Je sais qu’il existe un outil qui fera cela et beaucoup plus, ne peut pas penser à son nom. Mais c’est très cher.

Bonne chance. Je viens de comprendre ça. Je ne l’ai pas testé de façon approfondie avec les champs de texte, etc., mais il semble que cela vous amène un long chemin.

Greg

Il est possible de le faire via Visual Studio SQL Server Object Explorer.

Vous pouvez cliquer sur “Afficher les données” dans le menu contextuel pour obtenir la table nécessaire, filtrer les résultats et enregistrer le résultat sous forme de script.

1- Explication des scripts

A) La syntaxe pour insérer des données dans le tableau est comme ci-dessous

 Insert into table(col1,col2,col3,col4,col5) -- To achieve this part i --have used below variable ------@CSV_COLUMN------- values(Col1 data in quote, Col2..quote,..Col5..quote) -- To achieve this part -- ie column data in --quote i have used --below variable ----@QUOTED_DATA--- 

C) Pour obtenir les données ci-dessus de la table existante, nous devons écrire la requête select de manière à ce que la sortie soit sous la forme de scripts ci-dessus

D) Enfin, j’ai concaténé la variable ci-dessus pour créer le script final qui générera un script d’insertion à l’exécution

E)

 @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION 

F) Et enfin exécuté la requête ci-dessus EXECUTE(TEXT)

G) QUOTENAME() fonction QUOTENAME() est utilisée pour encapsuler les données de colonne entre guillemets

H) ISNULL est utilisé car si une ligne a des données NULL pour une colonne, la requête échoue et renvoie NULL pour éviter que j’aie utilisé ISNULL

I) Et créé le sp sp_generate_insertscripts pour le même

1- Mettez simplement le nom de la table pour lequel vous voulez insert script

2- Filtrer les conditions si vous voulez des résultats spécifiques

 ----------Final Procedure To generate Script------ CREATE PROCEDURE sp_generate_insertscripts ( @TABLE_NAME VARCHAR(MAX), @FILTER_CONDITION VARCHAR(MAX)='' ) AS BEGIN SET NOCOUNT ON DECLARE @CSV_COLUMN VARCHAR(MAX), @QUOTED_DATA VARCHAR(MAX), @TEXT VARCHAR(MAX) SELECT @CSV_COLUMN=STUFF ( ( SELECT ',['+ NAME +']' FROM sys.all_columns WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND is_identity!=1 FOR XML PATH('') ),1,1,'' ) SELECT @QUOTED_DATA=STUFF ( ( SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND is_identity!=1 FOR XML PATH('') ),1,1,'' ) SELECT @TEXT='SELECT ''INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)+'+'+''')'''+' Insert_Scripts FROM '+@TABLE_NAME + @FILTER_CONDITION --SELECT @CSV_COLUMN AS CSV_COLUMN,@QUOTED_DATA AS QUOTED_DATA,@TEXT TEXT EXECUTE (@TEXT) SET NOCOUNT OFF END 

Vous pouvez choisir l’option “Résultat dans un fichier” dans SSMS et exporter votre résultat sélectionné dans un fichier et apporter vos modifications dans le fichier de résultats et enfin utiliser BCP – Copie en bloc que vous pouvez insérer dans le tableau 1 de la firebase database 2.

Je pense que pour l’insertion en bloc, vous devez convertir le fichier .rpt en fichier .csv

J’espère que ça va aider.

Créer une table séparée en utilisant dans l’instruction Par exemple

Sélectionnez * dans Test_123 dans [dbo]. [Employé] où Nom comme ‘% Test%’

Aller à la firebase database Droite Cliquez sur la firebase database Cliquez sur Generate Script Sélectionnez votre table Sélectionnez l’option advanace et sélectionnez l’atsortingbut “Données uniquement” Sélectionnez le fichier “open in new query”

Sql va générer un script pour vous

Vous pouvez utiliser les packages de services d’intégration SQL Server spécialement conçus pour les opérations d’importation et d’exportation .

VS dispose d’un package pour développer ces packages si vous installez entièrement Sql Server.

Services d’intégration dans Business Intelligence Development Studio

Je pense que c’est également possible avec des requêtes ad hoc que vous pouvez exporter le fichier de résultats vers Excel, puis importer ce fichier dans votre object ou l’utiliser tel quel, puis importer le fichier Excel dans la deuxième firebase database. beaucoup.

http://vscontrols.blogspot.com/2010/09/import-and-export-excel-to-sql-server.html

Si vous utilisez Oracle (ou configurez l’application sur le serveur SQL), Oracle SQL Developer le fait pour vous. choisissez ‘unload’ pour une table et suivez les options (décochez DDL si vous ne voulez pas que toutes les choses soient créées).

J’ai trouvé cet addon SMSMS Boost , qui est gratuit et fait exactement cela entre autres. Vous pouvez cliquer avec le bouton droit sur les résultats et sélectionner Script data as.

Vous pouvez utiliser une INTO INSERT SELECT pour insérer les résultats d’une requête select dans une table. http://www.w3schools.com/sql/sql_insert_into_select.asp

Exemple:

 INSERT INTO Customers (CustomerName, Country) SELECT SupplierName, Country FROM Suppliers WHERE Country='Germany'; 

Vous pouvez utiliser ce Q2C.SSMSPlugin , gratuit et open source. Vous pouvez cliquer avec le bouton droit de la souris et sélectionner “Exécuter la requête pour commander … -> Requête pour insérer …”. Prendre plaisir)

J’ai eu un problème similaire, mais je devais pouvoir créer une instruction INSERT à partir d’une requête (avec des filtres, etc.)

J’ai donc créé la procédure suivante:

 CREATE PROCEDURE dbo.ConvertQueryToInsert (@input NVARCHAR(max), @target NVARCHAR(max)) AS BEGIN DECLARE @fields NVARCHAR(max); DECLARE @select NVARCHAR(max); -- Get the defintion from sys.columns and assemble a ssortingng with the fields/transformations for the dynamic query SELECT @fields = COALESCE(@fields + ', ', '') + '[' + name +']', @select = COALESCE(@select + ', ', '') + ''''''' + ISNULL(CAST([' + name + '] AS NVARCHAR(max)), ''NULL'')+''''''' FROM tempdb.sys.columns WHERE [object_id] = OBJECT_ID(N'tempdb..'+@input); -- Run the a dynamic query with the fields from @select into a new temp table CREATE TABLE #ConvertQueryToInsertTemp (ssortingngs nvarchar(max)) DECLARE @stmt NVARCHAR(max) = 'INSERT INTO #ConvertQueryToInsertTemp SELECT '''+ @select + ''' AS [ssortingngs] FROM '+@input exec sp_executesql @stmt -- Output the final insert statement SELECT 'INSERT INTO ' + @target + ' (' + @fields + ') VALUES (' + REPLACE(ssortingngs, '''NULL''', 'NULL') +')' FROM #ConvertQueryToInsertTemp -- Clean up temp tables DROP TABLE #ConvertQueryToInsertTemp SET @stmt = 'DROP TABLE ' + @input exec sp_executesql @stmt END 

Vous pouvez ensuite l’utiliser en écrivant la sortie de votre requête dans une table temporaire et en exécutant la procédure:

 -- Example table CREATE TABLE Dummy (Id INT, Comment NVARCHAR(50), TimeStamp DATETIME) INSERT INTO Dummy VALUES (1 , 'Foo', GetDate()), (2, 'Bar', GetDate()), (3, 'Foo Bar', GetDate()) -- Run query and procedure SELECT * INTO #TempTableForConvert FROM Dummy WHERE Id < 3 EXEC dbo.ConvertQueryToInsert '#TempTableForConvert', 'dbo.Dummy' 

Remarque: Cette procédure ne convertit les valeurs que dans une chaîne, ce qui peut donner un aspect différent aux données. Avec DATETIME, par exemple, les secondes seront perdues.

Ceci est une solution plus polyvalente (qui peut faire un peu plus que ce que la question demande), et peut être utilisée dans une fenêtre de requête sans avoir à créer un nouveau processus stocké, utile par exemple dans les bases de données de production .

Pour utiliser le code, veuillez modifier en fonction des commentaires en ligne qui expliquent son utilisation. Vous pouvez alors exécuter cette requête dans une fenêtre de requête et imprimer les instructions INSERT dont vous avez besoin.

 SET NOCOUNT ON -- Set the ID you wish to filter on here DECLARE @id AS INT = 123 DECLARE @tables TABLE (Name NVARCHAR(128), IdField NVARCHAR(128), IdInsert BIT, Excluded NVARCHAR(128)) -- Add any tables you wish to generate INSERT statements for here. The fields are as thus: -- Name: Your table name -- IdField: The field on which to filter the dataset -- IdInsert: If the primary key field is to be included in the INSERT statement -- Excluded: Any fields you do not wish to include in the INSERT statement INSERT INTO @tables (Name, IdField, IdInsert, Excluded) VALUES ('MyTable1', 'Id', 0, 'Created,Modified') INSERT INTO @tables (Name, IdField, IdInsert, Excluded) VALUES ('MyTable2', 'Id', 1, 'Created,Modified') DECLARE @numberTypes TABLE (sysId TINYINT) -- This will ensure INT and BIT types are not surrounded with quotes in the -- resultant INSERT statement, but you may need to add more (from sys.types) INSERT @numberTypes(SysId) VALUES(56),(104) DECLARE @rows INT = (SELECT COUNT(*) FROM @tables) DECLARE @cnt INT = 1 DECLARE @results TABLE (Sql NVARCHAR(4000)) WHILE @cnt <= @rows BEGIN DECLARE @tablename AS NVARCHAR(128) DECLARE @idField AS NVARCHAR(128) DECLARE @idInsert AS BIT DECLARE @excluded AS NVARCHAR(128) SELECT @tablename = Name, @idField = IdField, @idInsert = IdInsert, @excluded = Excluded FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS RowId FROM @tables) t WHERE t.RowId = @cnt DECLARE @excludedFields TABLE (FieldName NVARCHAR(128)) DECLARE @xml AS XML = CAST(('' + REPLACE(@excluded, ',', '') + '') AS XML) INSERT INTO @excludedFields SELECT N.value('.', 'NVARCHAR(128)') FROM @xml.nodes('X') AS T(N) DECLARE @setIdentity NVARCHAR(128) = 'SET IDENTITY_INSERT ' + @tablename DECLARE @execsql AS NVARCHAR(4000) = 'SELECT ''' + CASE WHEN @idInsert = 1 THEN @setIdentity + ' ON' + CHAR(13) ELSE '' END + 'INSERT INTO ' + @tablename + ' (' SELECT @execsql = @execsql + STUFF ( ( SELECT CASE WHEN NOT EXISTS(SELECT * FROM @excludedFields WHERE FieldName = name) THEN ', ' + name ELSE '' END FROM sys.columns WHERE object_id = OBJECT_ID('dbo.' + @tablename) FOR XML PATH('') ), 1, 2, '' ) + ')' + CHAR(13) + 'VALUES (' + STUFF ( ( SELECT CASE WHEN NOT EXISTS(SELECT * FROM @excludedFields WHERE FieldName = name) THEN ''', '' + ISNULL(' + CASE WHEN EXISTS(SELECT * FROM @numberTypes WHERE SysId = system_type_id) THEN '' ELSE ''''''''' + ' END + 'CAST(' + name + ' AS VARCHAR)' + CASE WHEN EXISTS(SELECT * FROM @numberTypes WHERE SysId = system_type_id) THEN '' ELSE ' + ''''''''' END + ', ''NULL'') + ' ELSE '' END FROM sys.columns WHERE object_id = OBJECT_ID('dbo.' + @tablename) FOR XML PATH('') ), 1, 3, '' ) + ''')' + CASE WHEN @idInsert = 1 THEN CHAR(13) + @setIdentity + ' OFF' ELSE '' END + ''' FROM ' + @tablename + ' WHERE ' + @idField + ' = ' + CAST(@id AS VARCHAR) INSERT @results EXEC (@execsql) DELETE @excludedFields SET @cnt = @cnt + 1 END DECLARE cur CURSOR FOR SELECT Sql FROM @results OPEN cur DECLARE @sql NVARCHAR(4000) FETCH NEXT FROM cur INTO @sql WHILE @@FETCH_STATUS = 0 BEGIN PRINT @sql FETCH NEXT FROM cur INTO @sql END CLOSE cur DEALLOCATE cur 

J’ai créé la procedure suivante:

 if object_id('tool.create_insert', 'P') is null begin exec('create procedure tool.create_insert as'); end; go alter procedure tool.create_insert(@schema varchar(200) = 'dbo', @table varchar(200), @where varchar(max) = null, @top int = null, @insert varchar(max) output) as begin declare @insert_fields varchar(max), @select varchar(max), @error varchar(500), @query varchar(max); declare @values table(description varchar(max)); set nocount on; -- Get columns select @insert_fields = isnull(@insert_fields + ', ', '') + c.name, @select = case type_name(c.system_type_id) when 'varchar' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + cast(' + c.name + ' as varchar) + '''''''', ''null'')' when 'datetime' then isnull(@select + ' + '', '' + ', '') + ' isnull('''''''' + convert(varchar, ' + c.name + ', 121) + '''''''', ''null'')' else isnull(@select + ' + '', '' + ', '') + 'isnull(cast(' + c.name + ' as varchar), ''null'')' end from sys.columns c with(nolock) inner join sys.tables t with(nolock) on t.object_id = c.object_id inner join sys.schemas s with(nolock) on s.schema_id = t.schema_id where s.name = @schema and t.name = @table; -- If there's no columns... if @insert_fields is null or @select is null begin set @error = 'There''s no ' + @schema + '.' + @table + ' inside the target database.'; raiserror(@error, 16, 1); return; end; set @insert_fields = 'insert into ' + @schema + '.' + @table + '(' + @insert_fields + ')'; if isnull(@where, '') <> '' and charindex('where', ltrim(rtrim(@where))) < 1 begin set @where = 'where ' + @where; end else begin set @where = ''; end; set @query = 'select ' + isnull('top(' + cast(@top as varchar) + ')', '') + @select + ' from ' + @schema + '.' + @table + ' with (nolock) ' + @where; insert into @values(description) exec(@query); set @insert = isnull(@insert + char(10), '') + '--' + upper(@schema + '.' + @table); select @insert = @insert + char(10) + @insert_fields + char(10) + 'values(' + v.description + ');' + char(10) + 'go' + char(10) from @values v where isnull(v.description, '') <> ''; end; go 

Ensuite, vous pouvez l’utiliser de cette façon:

 declare @insert varchar(max), @part varchar(max), @start int, @end int; set @start = 1; exec tool.create_insert @schema = 'dbo', @table = 'customer', @where = 'id = 1', @insert = @insert output; -- Print one line to avoid the maximum 8000 characters problem while len(@insert) > 0 begin set @end = charindex(char(10), @insert); if @end = 0 begin set @end = len(@insert) + 1; end; print subssortingng(@insert, @start, @end - 1); set @insert = subssortingng(@insert, @end + 1, len(@insert) - @end + 1); end; 

La sortie serait quelque chose comme ça:

 --DBO.CUSTOMER insert into dbo.customer(id, name, type) values(1, 'CUSTOMER NAME', 'F'); go 

Si vous voulez simplement obtenir une série de lignes, utilisez le paramètre @top comme @top :

 declare @insert varchar(max), @part varchar(max), @start int, @end int; set @start = 1; exec tool.create_insert @schema = 'dbo', @table = 'customer', @top = 100, @insert = @insert output; -- Print one line to avoid the maximum 8000 characters problem while len(@insert) > 0 begin set @end = charindex(char(10), @insert); if @end = 0 begin set @end = len(@insert) + 1; end; print subssortingng(@insert, @start, @end - 1); set @insert = subssortingng(@insert, @end + 1, len(@insert) - @end + 1); end;