plano - ¿Cómo exportar todos los datos de la tabla a un formato sql insertable?

importar archivo csv a sql server

Question

Tengo una tabla ( A_table ) en una base de datos ( A_db ) en Microsoft SQL Server Management Studio, y hay 10 filas.

Tengo otra base de datos ( B_db ) y tiene una tabla ( B_table ), que tiene la misma configuración de columna que tiene A_table . Pero la B_table está vacía.

Lo que quiero:

• Copie todas las filas de A_table a B_table .

¿Existe alguna opción en Microsoft SQL Server Management Studio 2012 para crear una inserción SQL desde una tabla? ¿O hay alguna otra opción para hacer eso?

Answer 1

Manera rápida y fácil:

1. Haga clic derecho en la base
2. Señalar tasks
3. Seleccionar generate scripts
4. Haga clic en Siguiente
5. Elige tablas
6. Haga clic en Siguiente
7. Haga clic avanzado
8. Desplácese a data to script : types of data to script llamados types of data to script en SMSS 2014 Gracias a Ellesedil por comentar
9. Seleccionar data only
10. Haga clic en ''Ok'' para cerrar la ventana de opciones de script avanzadas
11. Haga clic en Siguiente y genere su script

Normalmente, en casos como este, genero una nueva ventana del editor de consultas y luego hago cualquier modificación donde sea necesario.

Answer 2

No he visto ninguna opción en Microsoft SQL Server Management Studio 2012 hasta la fecha que haga eso.

Estoy seguro de que puedes escribir algo en T-SQL dado el tiempo.

Echa un vistazo a TOAD de QUEST , ahora propiedad de DELL.

http://www.toadworld.com/products/toad-for-oracle/f/10/t/9778.aspx

Select your rows.
Rt -click -> Export Dataset.
Choose Insert Statement format
Be sure to check “selected rows only”

Lo bueno de sapo, funciona tanto con el servidor SQL como con Oracle. Si tiene que trabajar con ambos, es una buena inversión.

Answer 3

Otra forma de volcar datos como archivo de la tabla mediante DumpDataFromTable sproc

EXEC dbo.DumpDataFromTable
     @SchemaName = ''dbo''
    ,@TableName = ''YourTableName''
    ,@PathOut = N''c:/tmp/scripts/' -- folder must exist !!!''

Nota: SQL debe tener permiso para crear archivos, si no está configurado, entonces exec sigue línea una vez

EXEC sp_configure ''Ole Automation Procedures'', 1; RECONFIGURE WITH OVERRIDE;

Con este script, puede llamar al sproc: DumpDataFromTable.sql y volcar más tablas de una vez, en lugar de hacerlo una a una manualmente desde Management Studio

Por defecto, el formato del scrip generado será como

INSERT INTO <TableName> SELECT <Values>

O puede cambiar el formato generado en

SELECT ... FROM

estableciendo la variable @BuildMethod = 2

código de sproc completo:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[DumpDataFromTable]'') AND type in (N''P'', N''PC''))
        DROP PROCEDURE dbo.[DumpDataFromTable]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    -- =============================================
    -- Author:    Oleg Ciobanu
    -- Create date: 20171214
    -- Version 1.0
    -- Description:
    -- dump data in 2 formats
    -- @BuildMethod = 1 INSERT INTO format
    -- @BuildMethod = 2 SELECT * FROM format
    --
    -- SQL must have permission to create files, if is not set-up then exec follow line once
    -- EXEC sp_configure ''Ole Automation Procedures'', 1; RECONFIGURE WITH OVERRIDE;
    --
    -- =============================================
    CREATE PROCEDURE [dbo].[DumpDataFromTable]
    (
         @SchemaName nvarchar(128) --= ''dbo''
        ,@TableName nvarchar(128) --= ''testTable''
        ,@WhereClause nvarchar (1000) = '''' -- must start with AND
        ,@BuildMethod int = 1 -- taking values 1 for INSERT INTO forrmat or 2 for SELECT from value Table
        ,@PathOut nvarchar(250) = N''c:/tmp/scripts/' -- folder must exist !!!''
        ,@AsFileNAme nvarchar(250) = NULL -- if is passed then will use this value as FileName
        ,@DebugMode int = 0
    )
    AS
    BEGIN  
        SET NOCOUNT ON;

            -- run follow next line if you get permission deny  for sp_OACreate,sp_OAMethod
            -- EXEC sp_configure ''Ole Automation Procedures'', 1; RECONFIGURE WITH OVERRIDE;

        DECLARE @Sql nvarchar (max)
        DECLARE @SqlInsert nvarchar (max) = ''''
        DECLARE @Columns nvarchar(max)
        DECLARE @ColumnsCast nvarchar(max)

        -- cleanUp/prepraring data
        SET @SchemaName = REPLACE(REPLACE(@SchemaName,''['',''''),'']'','''')
        SET @TableName = REPLACE(REPLACE(@TableName,''['',''''),'']'','''')
        SET @AsFileNAme = NULLIF(@AsFileNAme,'''')
        SET @AsFileNAme = REPLACE(@AsFileNAme,''.'',''_'')
        SET @AsFileNAme = COALESCE(@PathOut + @AsFileNAme + ''.sql'', @PathOut + @SchemaName + ISNULL(''_'' + @TableName,N'''') + ''.sql'')


        --debug
        IF @DebugMode = 1
            print @AsFileNAme

            -- Create temp SP what will be responsable for generating script files
        DECLARE @PRC_WritereadFile VARCHAR(max) =
            ''IF EXISTS (SELECT * FROM sys.objects WHERE type = ''''P'''' AND name = ''''PRC_WritereadFile'''')
           BEGIN
              DROP  Procedure  PRC_WritereadFile
           END;''
        EXEC  (@PRC_WritereadFile)
           -- ''  
        SET @PRC_WritereadFile =
        ''CREATE Procedure PRC_WritereadFile (
            @FileMode INT -- Recreate = 0 or Append Mode 1
           ,@Path NVARCHAR(1000)
           ,@AsFileNAme NVARCHAR(500)
           ,@FileBody NVARCHAR(MAX)   
           )
        AS
            DECLARE @OLEResult INT
            DECLARE @FS INT
            DECLARE @FileID INT
            DECLARE @hr INT
            DECLARE @FullFileName NVARCHAR(1500) = @Path + @AsFileNAme

            -- Create Object
            EXECUTE @OLEResult = sp_OACreate ''''Scripting.FileSystemObject'''', @FS OUTPUT
            IF @OLEResult <> 0 BEGIN
                PRINT ''''Scripting.FileSystemObject''''
                GOTO Error_Handler
            END    

            IF @FileMode = 0 BEGIN  -- Create
                EXECUTE @OLEResult = sp_OAMethod @FS,''''CreateTextFile'''',@FileID OUTPUT, @FullFileName
                IF @OLEResult <> 0 BEGIN
                    PRINT ''''CreateTextFile''''
                    GOTO Error_Handler
                END
            END ELSE BEGIN          -- Append
                EXECUTE @OLEResult = sp_OAMethod @FS,''''OpenTextFile'''',@FileID OUTPUT, @FullFileName, 8, 0 -- 8- forappending
                IF @OLEResult <> 0 BEGIN
                    PRINT ''''OpenTextFile''''
                    GOTO Error_Handler
                END            
            END

            EXECUTE @OLEResult = sp_OAMethod @FileID, ''''WriteLine'''', NULL, @FileBody
            IF @OLEResult <> 0 BEGIN
                PRINT ''''WriteLine''''
                GOTO Error_Handler
            END     

            EXECUTE @OLEResult = sp_OAMethod @FileID,''''Close''''
            IF @OLEResult <> 0 BEGIN
                PRINT ''''Close''''
                GOTO Error_Handler
            END

            EXECUTE sp_OADestroy @FS
            EXECUTE sp_OADestroy @FileID

            GOTO Done

            Error_Handler:
                DECLARE @source varchar(30), @desc varchar (200)       
                EXEC @hr = sp_OAGetErrorInfo null, @source OUT, @desc OUT
                PRINT ''''*** ERROR ***''''
                SELECT OLEResult = @OLEResult, hr = CONVERT (binary(4), @hr), source = @source, description = @desc

           Done:
        '';
            -- ''
        EXEC  (@PRC_WritereadFile) 
        EXEC PRC_WritereadFile 0 /*Create*/, '''', @AsFileNAme, ''''


        ;WITH steColumns AS (
            SELECT
                1 as rn,
                c.ORDINAL_POSITION
                ,c.COLUMN_NAME as ColumnName
                ,c.DATA_TYPE as ColumnType
            FROM INFORMATION_SCHEMA.COLUMNS c
            WHERE 1 = 1
            AND c.TABLE_SCHEMA = @SchemaName
            AND c.TABLE_NAME = @TableName
        )

        --SELECT *

           SELECT
                @ColumnsCast = ( SELECT
                                        CASE WHEN ColumnType IN (''date'',''time'',''datetime2'',''datetimeoffset'',''smalldatetime'',''datetime'',''timestamp'')
                                            THEN
                                                ''convert(nvarchar(1001), s.['' + ColumnName + '']'' + '' , 121) AS ['' + ColumnName + ''],''
                                                --,convert(nvarchar, [DateTimeScriptApplied], 121) as [DateTimeScriptApplied]
                                            ELSE
                                                ''CAST(s.['' + ColumnName + '']'' + '' AS NVARCHAR(1001)) AS ['' + ColumnName + ''],''
                                        END
                                         as ''data()''                                  
                                        FROM
                                          steColumns t2
                                        WHERE 1 =1
                                          AND t1.rn = t2.rn
                                        FOR xml PATH('''')
                                       )
                ,@Columns = ( SELECT
                                        ''['' + ColumnName + ''],'' as ''data()''                                  
                                        FROM
                                          steColumns t2
                                        WHERE 1 =1
                                          AND t1.rn = t2.rn
                                        FOR xml PATH('''')
                                       )

        FROM steColumns t1

        -- remove last char
        IF lEN(@Columns) > 0 BEGIN
            SET @Columns = SUBSTRING(@Columns, 1, LEN(@Columns)-1);
            SET @ColumnsCast = SUBSTRING(@ColumnsCast, 1, LEN(@ColumnsCast)-1);
        END

        -- debug
        IF @DebugMode = 1 BEGIN
            print @ColumnsCast
            print @Columns
            select @ColumnsCast ,  @Columns
        END

        -- build unpivoted Data
        SET @SQL = ''
        SELECT
            u.rn
            , c.ORDINAL_POSITION as ColumnPosition
            , c.DATA_TYPE as ColumnType
            , u.ColumnName
            , u.ColumnValue
        FROM
        (SELECT
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn,
        ''
        + CHAR(13) + @ColumnsCast
        + CHAR(13) + ''FROM ['' + @SchemaName + ''].['' + @TableName + ''] s''
        + CHAR(13) + ''WHERE 1 = 1''
        + CHAR(13) + COALESCE(@WhereClause,'''')
        + CHAR(13) + '') tt
        UNPIVOT
        (
          ColumnValue
          FOR ColumnName in (
        '' + CHAR(13) + @Columns
        + CHAR(13)
        + ''
         )
        ) u

        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = u.ColumnName
            AND c.TABLE_SCHEMA = ''''''+ @SchemaName + ''''''
            AND c.TABLE_NAME = '''''' + @TableName +''''''
        ORDER BY u.rn
                , c.ORDINAL_POSITION
        ''

        -- debug
        IF @DebugMode = 1 BEGIN
            print @Sql     
        END

        EXEC (@Sql)

        -- prepare data for cursor

        IF OBJECT_ID(''tempdb..#tmp'') IS NOT NULL
            DROP TABLE #tmp
        CREATE TABLE #tmp
        (
            rn bigint
            ,ColumnPosition int
            ,ColumnType varchar (128)
            ,ColumnName varchar (128)
            ,ColumnValue nvarchar (2000) -- I hope this size will be enough for storring values
        )
        SET @Sql = ''INSERT INTO  #tmp '' + CHAR(13)  + @Sql

        -- debug
        IF @DebugMode = 1 BEGIN
            print @Sql
        END

        EXEC (@Sql)

        IF @DebugMode = 1 BEGIN
            SELECT * FROM #tmp
        END

        DECLARE @rn bigint
            ,@ColumnPosition int
            ,@ColumnType varchar (128)
            ,@ColumnName varchar (128)
            ,@ColumnValue nvarchar (2000)
            ,@i int = -1 -- counter/flag
            ,@ColumnsInsert varchar(max) = NULL
            ,@ValuesInsert nvarchar(max) = NULL

        DECLARE cur CURSOR FOR
        SELECT rn, ColumnPosition, ColumnType, ColumnName, ColumnValue
        FROM #tmp
        ORDER BY rn, ColumnPosition -- note order is really important !!!
        OPEN cur

        FETCH NEXT FROM cur
        INTO @rn, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue

        IF @BuildMethod = 1
        BEGIN
            SET @SqlInsert = ''''
        END
        ELSE BEGIN
            SET @SqlInsert = ''SELECT *''
                            + CHAR(13) + ''FROM (''
                            + CHAR(13) + ''VALUES''
            EXEC PRC_WritereadFile 1 /*Add*/, '''', @AsFileNAme, @SqlInsert
            SET @SqlInsert = NULL
        END

        SET @i = @rn

        WHILE @@FETCH_STATUS = 0
        BEGIN

            IF (@i <> @rn) -- is a new row
            BEGIN
                IF @BuildMethod = 1
                -- build as INSERT INTO -- as Default
                BEGIN
                    SET @SqlInsert = ''INSERT INTO ['' + @SchemaName + ''].['' + @TableName + ''] (''
                                    + CHAR(13) + @ColumnsInsert + '')''
                                    + CHAR(13) + ''VALUES (''
                                    + @ValuesInsert
                                    + CHAR(13) + '')''
                END
                ELSE
                BEGIN
                    -- build as Table select
                    IF (@i <> @rn) -- is a new row
                    BEGIN
                        SET @SqlInsert = COALESCE(@SqlInsert + '','','''') +  ''('' + @ValuesInsert+ '')''
                        EXEC PRC_WritereadFile 1 /*Add*/, '''', @AsFileNAme, @SqlInsert
                        SET @SqlInsert = '''' -- in method 2 we should clear script
                    END            
                END
                -- debug
                IF @DebugMode = 1
                    PRINT @SqlInsert
                EXEC PRC_WritereadFile 1 /*Add*/, '''', @AsFileNAme, @SqlInsert

                -- we have new row
                -- initialise variables
                SET @i = @rn
                SET @ColumnsInsert = NULL
                SET @ValuesInsert = NULL
            END

            -- build insert values
            IF (@i = @rn) -- is same row
            BEGIN
                SET @ColumnsInsert = COALESCE(@ColumnsInsert + '','','''') + ''['' + @ColumnName + '']''
                SET @ValuesInsert =  CASE                              
                                        -- date
                                        --WHEN
                                        --  @ColumnType IN (''date'',''time'',''datetime2'',''datetimeoffset'',''smalldatetime'',''datetime'',''timestamp'')
                                        --THEN
                                        --  COALESCE(@ValuesInsert + '','','''') + '''''''''''' + ISNULL(RTRIM(@ColumnValue),''NULL'') + ''''''''''''
                                        -- numeric
                                        WHEN
                                            @ColumnType IN (''bit'',''tinyint'',''smallint'',''int'',''bigint''
                                                            ,''money'',''real'','''',''float'',''decimal'',''numeric'',''smallmoney'')
                                        THEN
                                            COALESCE(@ValuesInsert + '','','''') + '''' + ISNULL(RTRIM(@ColumnValue),''NULL'') + ''''
                                        -- other types treat as string
                                        ELSE
                                            COALESCE(@ValuesInsert + '','','''') + '''''''' + ISNULL(RTRIM(@ColumnValue),''NULL'') + ''''''''    
                                    END
            END


            FETCH NEXT FROM cur
            INTO @rn, @ColumnPosition, @ColumnType, @ColumnName, @ColumnValue

            -- debug
            IF @DebugMode = 1
            BEGIN
                print CAST(@rn AS VARCHAR) + ''-'' + CAST(@ColumnPosition AS VARCHAR)
            END
        END
        CLOSE cur
        DEALLOCATE cur

        IF @BuildMethod = 1
        BEGIN
            PRINT ''ignore''
        END
        ELSE BEGIN
            SET @SqlInsert = CHAR(13) + '') AS vtable ''
                            + CHAR(13) + '' ('' + @Columns
                            + CHAR(13) + '')''
            EXEC PRC_WritereadFile 1 /*Add*/, '''', @AsFileNAme, @SqlInsert
            SET @SqlInsert = NULL
        END
    END

o puede descargarse desde https://github.com/Zindur/MSSQL-DumpTable/tree/master/Scripts

Answer 4

Pruebe www.sqlscripter.com.

Por lo que yo sé, es la única herramienta que no solo crea scripts INSERT de una sola vez, sino que también puede generar INSERT y UPDATE y scripts combinados para actualizaciones frecuentes. Lo estamos usando desde hace un par de años y funciona perfecto para nosotros.

Answer 5

Sé que esta es una vieja pregunta, pero victorio también preguntó si hay otras opciones para copiar datos de una tabla a otra. Hay una manera muy corta y rápida de insertar todos los registros de una tabla a otra (que pueden tener o no diseño similar).

Si no tiene una columna de identidad en la tabla B_table:

INSERT INTO A_db.dbo.A_table
SELECT * FROM B_db.dbo.B_table

Si tiene una columna de identidad en la tabla B_table, debe especificar columnas para insertar. Básicamente, selecciona todos excepto la columna de identidad, que se incrementará automáticamente de forma predeterminada.

En caso de que no tenga una B_table en B_db

SELECT *
INTO B_db.dbo.B_table
FROM A_db.dbo.A_table

creará tabla B_table en la base de datos B_db con todos los valores existentes