update - ¿Cómo generar un script INSERT para una tabla de SQL Server existente que incluya todas las filas almacenadas?

modificar valores de una tabla sql

Question

Estoy buscando una manera de generar un script "Crear e insertar todas las filas" con SQL Management Studio 2008 R2.

Sé que puedo crear una secuencia de comandos "crear tabla".

También puedo crear un script "insertar en", pero eso solo generará una fila con marcadores de posición.

¿Hay alguna forma de generar un script de inserción que contenga todas las filas almacenadas actualmente?

Answer 1

Este script genera declaraciones de inserción de sus datos existentes. Este es un procedimiento almacenado que necesita ejecutar una vez y luego está hecho a medida para usted.

Intenté encontrar este tipo de cosas por un tiempo, pero no estaba satisfecho con los resultados, así que escribí este procedimiento almacenado.

Ejemplo:

Exec [dbo].[INS]  ''Dbo.test where 1=1''

(1) Aquí dbo es esquema y prueba es nombre de tabla y 1=1 es condición.

Exec [dbo].[INS]  ''Dbo.test where name =''''neeraj'''''' * for string

(2) Aquí dbo es esquema y prueba es name=''neeraj'' tabla y name=''neeraj'' es condición.

Aquí está el procedimiento almacenado

/*
Authore : neeraj prasad sharma (please dont remove this :))
Example (1) Exec [dbo].[INS]  ''Dbo.test where 1=1''
        (2) Exec [dbo].[INS]  ''Dbo.test where name =''''neeraj'''''' * for string

here Dbo is schema and test is tablename and 1=1 is condition

*/


CREATE procedure  [dbo].[INS]                              
(                                                          
   @Query  Varchar(MAX)                                                          
)                              

AS                              

SET nocount ON                  

DECLARE @WithStrINdex as INT                            
DECLARE @WhereStrINdex as INT                            
DECLARE @INDExtouse as INT                            

DECLARE @SchemaAndTAble VArchar(270)                            
DECLARE @Schema_name  varchar(30)                            
DECLARE @Table_name  varchar(240)                            
DECLARE @Condition  Varchar(MAX)                             

SET @WithStrINdex=0                            

SELECT @WithStrINdex=CHARINDEX(''With'',@Query )                            
, @WhereStrINdex=CHARINDEX(''WHERE'', @Query)                            

IF(@WithStrINdex!=0)                            
SELECT @INDExtouse=@WithStrINdex                            
ELSE                            
SELECT @INDExtouse=@WhereStrINdex                            

SELECT @SchemaAndTAble=Left (@Query,@INDExtouse-1)                                                     
SELECT @SchemaAndTAble=Ltrim (Rtrim( @SchemaAndTAble))                            

SELECT @Schema_name= Left (@SchemaAndTAble, CharIndex(''.'',@SchemaAndTAble )-1)                            
,      @Table_name = SUBSTRING(  @SchemaAndTAble , CharIndex(''.'',@SchemaAndTAble )+1,LEN(@SchemaAndTAble) )                            

,      @CONDITION=SUBSTRING(@Query,@WhereStrINdex+6,LEN(@Query))--27+6                            


DECLARE @COLUMNS  table (Row_number SmallINT , Column_Name VArchar(Max) )                              
DECLARE @CONDITIONS as varchar(MAX)                              
DECLARE @Total_Rows as SmallINT                              
DECLARE @Counter as SmallINT              

DECLARE @ComaCol as varchar(max)            
SELECT @ComaCol=''''                   

SET @Counter=1                              
SET @CONDITIONS=''''                              

INSERT INTO @COLUMNS                              
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count], Column_Name 
FROM INformation_schema.columns 
WHERE Table_schema=@Schema_name AND table_name=@Table_name         


SELECT @Total_Rows= Count(1) 
FROM @COLUMNS                              

SELECT @Table_name= ''[''+@Table_name+'']''                      

SELECT @Schema_name=''[''+@Schema_name+'']''                      

While (@Counter<=@Total_Rows )                              
begin                               
--PRINT @Counter                              

SELECT @ComaCol= @ComaCol+''[''+Column_Name+''],''            
FROM @COLUMNS                              
WHERE [Row_number]=@Counter                          

SELECT @CONDITIONS=@CONDITIONS+ '' + Case When [''+Column_Name+''] is null then ''''Null'''' Else '''''''''''''''' + Replace( Convert(varchar(Max),[''+Column_Name+'']  ) ,'''''''''''''''',''''''''  ) +'''''''''''''''' end+''+'''''',''''''                                                     
FROM @COLUMNS                              
WHERE [Row_number]=@Counter                              

SET @Counter=@Counter+1                              

End                              

SELECT @CONDITIONS=Right(@CONDITIONS,LEN(@CONDITIONS)-2)                              

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)              
SELECT @ComaCol= substring (@ComaCol,0,  len(@ComaCol) )                            

SELECT @CONDITIONS= ''''''INSERT INTO ''+@Schema_name+''.''+@Table_name+ ''(''+@ComaCol+'')'' +'' Values( ''+'''''''' + ''+''+@CONDITIONS                              

SELECT @CONDITIONS=@CONDITIONS+''+''+ '''''')''''''                              

SELECT @CONDITIONS= ''Select  ''+@CONDITIONS +''FRom  '' +@Schema_name+''.''+@Table_name+'' With(NOLOCK) '' + '' Where ''+@Condition                              
print(@CONDITIONS)                              
Exec(@CONDITIONS)  

Answer 2

Sí, pero tendrás que ejecutarlo en el nivel de la base de datos.

Haga clic derecho en la base de datos en SSMS, seleccione "Tareas", "Generar secuencias de comandos ...". Mientras trabajas, llegarás a la sección "Opciones de secuencias de comandos". Haga clic en "Avanzado", y en la lista que aparece, donde dice "Tipos de datos a secuencia de comandos", tiene la opción de seleccionar Datos y / o Esquema.

Answer 3

Sí, use el complemento comercial, pero económico, del Paquete de herramientas SSMS, que tiene una característica ingeniosa de "Generar inserciones desde resultados, tablas o bases de datos"

Answer 4

Solo para compartir, he desarrollado mi propio script para hacerlo. Sientase libre de usarlo. Genera instrucciones "SELECT" que luego puede ejecutar en las tablas para generar las declaraciones "INSERT".

select distinct ''SELECT ''''INSERT INTO '' + schema_name(ta.schema_id) + ''.'' + so.name + '' ('' + substring(o.list, 1, len(o.list)-1) + '') VALUES (''
+ substring(val.list, 1, len(val.list)-1) + '');''''  FROM '' + schema_name(ta.schema_id) + ''.'' + so.name + '';''
from    sys.objects so
join sys.tables ta on ta.object_id=so.object_id
cross apply
(SELECT ''  '' +column_name + '', '' 
 from information_schema.columns c
 join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
 where table_name = so.name
 order by ordinal_position
FOR XML PATH('''')) o (list)
cross apply
(SELECT ''''''+'' +case 
         when data_type = ''uniqueidentifier'' THEN ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''])+'''''''''''''''' END '' 
         WHEN data_type = ''timestamp'' then ''''''''''''''''''+CONVERT(NVARCHAR(MAX),CONVERT(BINARY(8),['' + COLUMN_NAME + '']),1)+'''''''''''''''''' 
         WHEN data_type = ''nvarchar'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+REPLACE(['' + COLUMN_NAME + ''],'''''''''''''''','''''''''''''''''''''''')+'''''''''''''''' END''
         WHEN data_type = ''varchar'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+REPLACE(['' + COLUMN_NAME + ''],'''''''''''''''','''''''''''''''''''''''')+'''''''''''''''' END''
         WHEN data_type = ''char'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+REPLACE(['' + COLUMN_NAME + ''],'''''''''''''''','''''''''''''''''''''''')+'''''''''''''''' END''
         WHEN data_type = ''nchar'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+REPLACE(['' + COLUMN_NAME + ''],'''''''''''''''','''''''''''''''''''''''')+'''''''''''''''' END''
         when DATA_TYPE=''datetime'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''],121)+'''''''''''''''' END ''
         when DATA_TYPE=''datetime2'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''],121)+'''''''''''''''' END ''
         when DATA_TYPE=''geography'' and column_name<>''Shape'' then ''ST_GeomFromText(''''POINT(''+column_name+''.Lat ''+column_name+''.Long)'''') ''
         when DATA_TYPE=''geography'' and column_name=''Shape'' then ''''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''])+''''''''''''''''''
         when DATA_TYPE=''bit'' then ''''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''])+''''''''''''''''''
         when DATA_TYPE=''xml'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+REPLACE(CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + '']),'''''''''''''''','''''''''''''''''''''''')+'''''''''''''''' END ''
         WHEN DATA_TYPE=''image'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),CONVERT(VARBINARY(MAX),['' + COLUMN_NAME + '']),1)+'''''''''''''''' END ''
         WHEN DATA_TYPE=''varbinary'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''],1)+'''''''''''''''' END ''
         WHEN DATA_TYPE=''binary'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''],1)+'''''''''''''''' END ''
         when DATA_TYPE=''time'' then ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE ''''''''''''''''+CONVERT(NVARCHAR(MAX),['' + COLUMN_NAME + ''])+'''''''''''''''' END ''
         ELSE ''CASE WHEN ['' + column_name+''] IS NULL THEN ''''NULL'''' ELSE CONVERT(NVARCHAR(MAX),[''+column_name+'']) END'' end
   + ''+'''', ''  
 from information_schema.columns c
 join syscolumns co on co.name=c.COLUMN_NAME and object_name(co.id)=so.name and OBJECT_NAME(co.id)=c.TABLE_NAME and co.id=so.object_id and c.TABLE_SCHEMA=SCHEMA_NAME(so.schema_id)
 where table_name = so.name
 order by ordinal_position
FOR XML PATH('''')) val (list)
where   so.type = ''U''