teclas - Conversión de Seleccionar resultados en secuencia de comandos Insertar-SQL Server
teclas rapidas sql server 2012 (18)
Tengo SQL Server 2008 , SQL Server Management Studio.
Necesito seleccionar datos de Table1 en database1. Luego, tengo que editar algunos valores en los resultados e insert values into Table1 en database2.
O déjame decirlo de otra manera.
¿Cómo puedo convertir los datos en una tabla en insert script ?
1- Explicación de guiones
A) La sintaxis para insertar datos en la tabla es la siguiente
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
-- i.e column data in
--quote i have used
--below variable
----@QUOTED_DATA---
C) Para obtener los datos anteriores de la tabla existente, debemos escribir la consulta de selección de tal manera que la salida esté en la forma de los scripts anteriores.
D) Entonces finalmente he concatenado la variable anterior para crear el script final que generará el script de inserción en la ejecución
MI)
@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) Y finalmente ejecutó la consulta anterior EXECUTE(TEXT)
G) QUOTENAME() función QUOTENAME() se usa para ajustar los datos de la columna dentro de la cita
H) Se usa ISNULL porque si cualquier fila tiene datos NULL para cualquier columna, la consulta falla y devuelve NULL por eso, para evitar que haya usado ISNULL
I) Y creé sp sp_generate_insertscripts para el mismo
1- Simplemente ponga el nombre de la tabla para la cual desea insert script
2- Condición del filtro si quieres resultados específicos
----------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
Aquí hay otro método, que puede ser más fácil que instalar plugins o herramientas externas en algunas situaciones:
- Haga una
select [whatever you need]INTO temp.table_namefrom [... etc ...]. - Haga clic derecho en la base de datos en el Explorador de objetos => Tareas => Generar secuencias de comandos
- Seleccione
temp.table_nameen la pantalla "Elegir objetos", haga clic en Siguiente. - En la pantalla "Especificar cómo se deben guardar los scripts":
- Haga clic en Avanzado, busque la propiedad "Tipos de datos para la secuencia de comandos", seleccione "Solo datos", cierre las propiedades avanzadas.
- Seleccione "Guardar en nueva ventana de consulta" (a menos que tenga miles de registros).
- Haga clic en Siguiente, espere a que se complete el trabajo, observe que las
INSERTresultantes aparecen en una nueva ventana de consulta. - Use Buscar y reemplazar para cambiar todo
[temp.table_name]a[your_table_name]. -
drop table [temp.table_name].
Creé el siguiente procedure :
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
Entonces puedes usarlo de esa manera:
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 substring(@insert, @start, @end - 1);
set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;
La salida sería algo así:
--DBO.CUSTOMER
insert into dbo.customer(id, name, type)
values(1, ''CUSTOMER NAME'', ''F'');
go
Si solo desea obtener un rango de filas, use el parámetro @top como sigue:
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 substring(@insert, @start, @end - 1);
set @insert = substring(@insert, @end + 1, len(@insert) - @end + 1);
end;
Cree una tabla separada usando en la declaración Por ejemplo
Seleccione * en Prueba_123 desde [dbo]. [Empleado] donde Nombre como ''% Prueba%''
Vaya a la Base de Datos Haga clic con el botón derecho en la Base de Datos Haga clic en Generar Script Seleccione su tabla Seleccione la opción advanace y seleccione el atributo "Solo Datos" Seleccione el archivo "abrir en una nueva consulta"
Sql generará un script para ti
Creo que también es posible con consultas adhoc, puede exportar el resultado al archivo excel y luego importar ese archivo en su objeto datatable o usarlo tal como está y luego importar el archivo excel en la segunda base de datos. Mire este enlace. Esto puede ayudarlo. mucho.
http://vscontrols.blogspot.com/2010/09/import-and-export-excel-to-sql-server.html
En SSMS:
Haga clic con el botón derecho en la base de datos> Tareas> Generar secuencias de comandos
Siguiente
Seleccione "Seleccionar objetos específicos de la base de datos" y verifique la tabla que desea generar, Next
Haga clic en
Advanced >en la lista de opciones, desplácese hacia abajo hasta la parte inferior y busque los "Tipos de datos para secuencia de comandos" y cámbielo a "Solo datos"> AceptarSeleccione "Guardar en nueva ventana de consulta"> Siguiente> Siguiente> Finalizar
¡Las 180 filas ahora escritas como 180 inserciones!
Encontré este complemento SMSMS Boost , que es gratis y hace exactamente esto, entre otras cosas. Puede hacer clic con el botón derecho en los resultados y seleccionar Datos de secuencia de comandos como.
Es posible hacerlo a través de Visual Studio SQL Server Object Explorer.
Puede hacer clic en "Ver datos" en el menú contextual para ver la tabla necesaria, filtrar los resultados y guardar el resultado como secuencia de comandos.
Esta es una solución más versátil (que puede hacer un poco más de lo que la pregunta), y puede usarse en una ventana de consulta sin tener que crear un nuevo proceso almacenado, útil en bases de datos de producción, por ejemplo, donde no tiene acceso de escritura .
Para usar el código, modifíquelo de acuerdo con los comentarios en línea que explican su uso. A continuación, puede ejecutar esta consulta en una ventana de consulta e imprimirá las instrucciones INSERT que necesite.
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((''<X>'' + REPLACE(@excluded, '','', ''</X><X>'') + ''</X>'') 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
Puede elegir la opción ''Resultado para archivo'' en SSMS y exportar su resultado de selección al archivo y hacer los cambios en el archivo de resultados y finalmente usar BCP - Copia masiva que puede insertar en la tabla 1 en la base de datos 2.
Creo que para la inserción masiva tienes que convertir el archivo .rpt al archivo .csv
Espero que ayude
Puede usar este Q2C.SSMSPlugin , que es de código abierto y gratuito. Puede hacer clic derecho y seleccionar "Ejecutar consulta al comando ... -> Consultar para insertar ...". Disfrutar)
Puede usar los paquetes del servicio de integración de servidor SQL específicamente diseñados para la operación de importación y exportación .
VS tiene un paquete para desarrollar estos paquetes si instala completamente el servidor Sql.
Servicios de integración en Business Intelligence Development Studio
Puede usar una INTO INSERT SELECT para insertar los resultados de una consulta de selección en una tabla. http://www.w3schools.com/sql/sql_insert_into_select.asp
Ejemplo:
INSERT INTO Customers (CustomerName, Country) SELECT SupplierName, Country FROM Suppliers WHERE Country=''Germany'';
SSMS Toolpack (que es GRATUITO como en beer) tiene una variedad de excelentes características, incluida la generación de sentencias INSERT a partir de tablas.
Actualización: para SQL Server Management Studio 2012 (y más reciente), SSMS Toolpack ya no es gratuito, pero requiere una tarifa de licencia modesta.
Si está utilizando Oracle (o configura la aplicación en el servidor SQL), Oracle SQL Developer hace esto por usted. elija ''descargar'' para una tabla y siga las opciones hasta (desmarque DDL si no desea que toda la tabla cree cosas).
Tenía un problema similar, pero necesitaba poder crear una instrucción INSERT a partir de una consulta (con filtros, etc.)
Así que creé el siguiente procedimiento:
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 string 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 (strings nvarchar(max))
DECLARE @stmt NVARCHAR(max) = ''INSERT INTO #ConvertQueryToInsertTemp SELECT ''''''+ @select + '''''' AS [strings] FROM ''+@input
exec sp_executesql @stmt
-- Output the final insert statement
SELECT ''INSERT INTO '' + @target + '' ('' + @fields + '') VALUES ('' + REPLACE(strings, ''''''NULL'''''', ''NULL'') +'')'' FROM #ConvertQueryToInsertTemp
-- Clean up temp tables
DROP TABLE #ConvertQueryToInsertTemp
SET @stmt = ''DROP TABLE '' + @input
exec sp_executesql @stmt
END
Luego puede usarlo escribiendo el resultado de su consulta en una tabla temporal y ejecutando el procedimiento:
-- 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''
Nota: Este procedimiento solo arroja los valores a una cadena que puede hacer que los datos se vean un poco diferentes. Con DATETIME, por ejemplo, los segundos se perderán.
Usando Visual Studio, haz lo siguiente
Cree un proyecto de tipo SQL Server -> Proyecto de Base de Datos SQL Server
abra el explorador de servidor sql CTL- /, CTL-S
agregue un servidor SQL haciendo clic derecho en el icono SQL SERVER. Selcet AGREGAR NUEVO SERVIDOR
navega hasta la tabla que te interesa
clic derecho -> VER DATOS
Haga clic en la celda superior izquierda para resaltar todo (ctl-A parece que no funciona)
Click derecho -> SCript
Esto es fabuloso. He intentado todo lo mencionado anteriormente a lo largo de los años. Sé que hay una herramienta que hará esto y mucho más, no puedo pensar en el nombre. Pero es muy caro.
Buena suerte. Me di cuenta de esto. No lo he probado extensamente con campos de texto, etc., pero parece que te lleva lejos en el camino.
Greg
Método nativo :
por ejemplo, si tienes una mesa
Users(Id, name)
Puedes hacerlo:
select ''insert into Table values(Id='' + Id + '', name='' + name + '')'' from Users