stored - tabla temporal sql
Insertar los resultados de un procedimiento almacenado en una tabla temporal (25)
¿Cómo hago un SELECT * INTO [temp table] FROM [stored procedure] ? ¿NO FROM [Table] y sin definir [temp table] ?
Select todos los datos de BusinessLine en tmpBusLine funciona bien.
select *
into tmpBusLine
from BusinessLine
Estoy intentando lo mismo, pero usar un stored procedure que devuelve datos, no es exactamente lo mismo.
select *
into tmpBusLine
from
exec getBusinessLineHistory ''16 Mar 2009''
Mensaje de salida:
Msg 156, Nivel 15, Estado 1, Línea 2 Sintaxis incorrecta cerca de la palabra clave ''exec''.
He leído varios ejemplos de cómo crear una tabla temporal con la misma estructura que el procedimiento almacenado de salida, que funciona bien, pero sería bueno no suministrar ninguna columna.
Solución más fácil:
CREATE TABLE #temp (...); INSERT INTO #temp EXEC [sproc];
Si no conoce el esquema, puede hacer lo siguiente. Tenga en cuenta que existen graves riesgos de seguridad en este método.
SELECT *
INTO #temp
FROM OPENROWSET(''SQLNCLI'',
''Server=localhost;Trusted_Connection=yes;'',
''EXEC [db].[schema].[sproc]'')
- Estoy creando una tabla con el siguiente esquema y datos.
- Crear un procedimiento almacenado.
Ahora sé cuál es el resultado de mi procedimiento, por lo que estoy realizando la siguiente consulta.
CREATE TABLE [dbo].[tblTestingTree]( [Id] [int] IDENTITY(1,1) NOT NULL, [ParentId] [int] NULL, [IsLeft] [bit] NULL, [IsRight] [bit] NULL, CONSTRAINT [PK_tblTestingTree] PRIMARY KEY CLUSTERED ( [Id] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[tblTestingTree] ON INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (1, NULL, NULL, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (2, 1, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (3, 1, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (4, 2, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (5, 2, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (6, 3, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (7, 3, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (8, 4, 1, NULL) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (9, 4, NULL, 1) INSERT [dbo].[tblTestingTree] ([Id], [ParentId], [IsLeft], [IsRight]) VALUES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo].[tblTestingTree] OFFVALORES (10, 5, 1, NULL) SET IDENTITY_INSERT [dbo]. [TblTestingTree] On
create procedure GetDate as begin select Id,ParentId from tblTestingTree end create table tbltemp ( id int, ParentId int ) insert into tbltemp exec GetDate select * from tbltemp;
¿Su procedimiento almacenado solo recupera los datos o también los modifica? Si se usa solo para recuperar, puede convertir el procedimiento almacenado en una función y usar las Expresiones de tabla comunes (CTE) sin tener que declararlo, de la siguiente manera:
with temp as (
select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp
Sin embargo, todo lo que deba recuperarse del CTE debe usarse en una sola declaración. No puede hacer una with temp as ... y tratar de usarla después de un par de líneas de SQL. Puede tener varios CTE en una declaración para consultas más complejas.
Por ejemplo,
with temp1020 as (
select id from dbo.fnFunctionName(10, 20)
),
temp2030 as (
select id from dbo.fnFunctionName(20, 30)
)
select * from temp1020
where id not in (select id from temp2030)
Cuando el procedimiento almacenado devuelve muchas columnas y no desea "crear" manualmente una tabla temporal para contener el resultado, he encontrado que la forma más sencilla es ingresar al procedimiento almacenado y agregar una cláusula "into" en la última declaración de selección y agregue 1 = 0 a la cláusula where.
Ejecute el procedimiento almacenado una vez y vuelva atrás y elimine el código SQL que acaba de agregar. Ahora, tendrá una tabla vacía que coincide con el resultado del procedimiento almacenado. Podría "crear una tabla de scripts como creación" para una tabla temporal o simplemente insertarla directamente en esa tabla.
En SQL Server 2005 puede usar INSERT INTO ... EXEC para insertar el resultado de un procedimiento almacenado en una tabla. De la documentación INSERT de MSDN (para SQL Server 2000, de hecho):
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
Encontré Pasar matrices / Tablas de datos en procedimientos almacenados que podrían darle otra idea de cómo podría resolver su problema.
El enlace sugiere utilizar un parámetro de tipo de imagen para pasar al procedimiento almacenado. Luego, en el procedimiento almacenado, la imagen se transforma en una variable de tabla que contiene los datos originales.
Tal vez haya una forma en que esto se puede utilizar con una tabla temporal.
Esta es una respuesta a una versión ligeramente modificada de su pregunta. Si puede abandonar el uso de un procedimiento almacenado para una función definida por el usuario, puede usar una función definida por el usuario con valores de tabla en línea. Este es esencialmente un procedimiento almacenado (tomará parámetros) que devuelve una tabla como un conjunto de resultados; y por lo tanto se colocará muy bien con una declaración INTO.
Aquí hay un buen artículo rápido sobre él y otras funciones definidas por el usuario. Si aún tiene la necesidad de conducir un procedimiento almacenado, puede ajustar la función definida por el usuario con valores de tabla en línea con un procedimiento almacenado. El procedimiento almacenado simplemente pasa parámetros cuando llama a la función select * de la tabla en línea definida por el usuario.
Entonces, por ejemplo, tendría una función definida por el usuario con valores de tabla en línea para obtener una lista de clientes para una región en particular:
CREATE FUNCTION CustomersByRegion
(
@RegionID int
)
RETURNS TABLE
AS
RETURN
SELECT *
FROM customers
WHERE RegionID = @RegionID
GO
A continuación, puede llamar a esta función para obtener sus resultados:
SELECT * FROM CustomersbyRegion(1)
O hacer un SELECT INTO:
SELECT * INTO CustList FROM CustomersbyRegion(1)
Si aún necesita un procedimiento almacenado, envuelva la función como tal:
CREATE PROCEDURE uspCustomersByRegion
(
@regionID int
)
AS
BEGIN
SELECT * FROM CustomersbyRegion(@regionID);
END
GO
Creo que este es el método más ''hack-less'' para obtener los resultados deseados. Utiliza las características existentes como estaban destinadas a ser utilizadas sin complicaciones adicionales. Al anidar la función definida por el usuario con valores de tabla en línea en el procedimiento almacenado, tiene acceso a la funcionalidad de dos maneras. ¡Más! Solo tiene un punto de mantenimiento para el código SQL real.
Se ha sugerido el uso de OPENROWSET, pero esto no es para lo que se diseñó la función OPENROWSET (de Libros en línea):
Incluye toda la información de conexión que se requiere para acceder a datos remotos desde una fuente de datos OLE DB. Este método es una alternativa al acceso a las tablas en un servidor vinculado y es un método ad hoc de una sola vez para conectar y acceder a datos remotos mediante el uso de OLE DB. Para referencias más frecuentes a las fuentes de datos OLE DB, use servidores vinculados en su lugar.
El uso de OPENROWSET hará el trabajo, pero incurrirá en una sobrecarga adicional para abrir conexiones locales y ordenar datos. Tampoco puede ser una opción en todos los casos, ya que requiere un permiso de consulta ad hoc que plantea un riesgo de seguridad y, por lo tanto, puede no ser deseable. Además, el enfoque de OPENROWSET impedirá el uso de procedimientos almacenados que arrojen más de un conjunto de resultados. El ajuste de múltiples funciones definidas por el usuario con valores de tabla en línea en un solo procedimiento almacenado puede lograr esto.
Este proc almacenado hace el trabajo:
CREATE PROCEDURE [dbo].[ExecIntoTable]
(
@tableName NVARCHAR(256),
@storedProcWithParameters NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @driver VARCHAR(10)
DECLARE @connectionString NVARCHAR(600)
DECLARE @sql NVARCHAR(MAX)
DECLARE @rowsetSql NVARCHAR(MAX)
SET @driver = ''''''SQLNCLI''''''
SET @connectionString =
''''''server='' +
CAST(SERVERPROPERTY(''ServerName'') AS NVARCHAR(256)) +
COALESCE(''/' + CAST(SERVERPROPERTY(''InstanceName'') AS NVARCHAR(256)), '''') +
'';trusted_connection=yes''''''
SET @rowsetSql = ''''''EXEC '' + REPLACE(@storedProcWithParameters, '''''''', '''''''''''') + ''''''''
SET @sql = ''
SELECT
*
INTO
'' + @tableName + ''
FROM
OPENROWSET('' + @driver + '','' + @connectionString + '','' + @rowsetSql + '')''
EXEC (@sql)
END
GO
Es una pequeña modificación de esto: inserte los resultados del procedimiento almacenado en la tabla para que realmente funcione.
Si desea que funcione con una tabla temporal, deberá usar una tabla ##GLOBAL y luego soltarla.
Para insertar el primer conjunto de registros de un procedimiento almacenado en una tabla temporal, necesita saber lo siguiente:
- solo el primer conjunto de filas del procedimiento almacenado se puede insertar en una tabla temporal
- el procedimiento almacenado no debe ejecutar la sentencia T-SQL dinámica (
sp_executesql) - Es necesario definir primero la estructura de la tabla temporal.
Lo anterior puede parecer una limitación, pero en mi humilde opinión tiene mucho sentido: si está usando sp_executesql , una vez puede devolver dos columnas y una vez diez, y si tiene varios conjuntos de resultados, no puede insertarlas en varias tablas también, puede insertar máximo en dos tablas en una instrucción T-SQL (usando la cláusula OUTPUT y sin activadores).
Por lo tanto, el problema es principalmente cómo definir la estructura de la tabla temporal antes de ejecutar la instrucción EXEC ... INTO ...
- sys.dm_exec_describe_first_result_set_for_object
- sys.dm_exec_describe_first_result_set
- sp_describe_first_result_set
El primero funciona con OBJECT_ID mientras que el segundo y el tercero también funcionan con consultas Ad-hoc. Prefiero usar el DMV en lugar del SP, ya que puede usar CROSS APPLY y construir las definiciones de tablas temporales para múltiples procedimientos al mismo tiempo.
SELECT p.name, r.*
FROM sys.procedures AS p
CROSS APPLY sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r;
Además, preste atención al campo system_type_name , ya que puede ser muy útil. Almacena la columna de definición completa. Por ejemplo:
smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)
y puede usarlo directamente en la mayoría de los casos para crear la definición de la tabla.
Por lo tanto, creo que en la mayoría de los casos (si el procedimiento almacenado coincide con ciertos criterios) puede crear fácilmente declaraciones dinámicas para resolver tales problemas (cree la tabla temporal, inserte el resultado del procedimiento almacenado, haga lo que necesite con los datos) .
Tenga en cuenta que los objetos anteriores no pueden definir los datos del primer conjunto de resultados en algunos cases como cuando se ejecutan sentencias de T-SQL dinámicas o se usan tablas temporales en el procedimiento almacenado.
Puede utilizar OPENROWSET para esto. Echar un vistazo. También he incluido el código sp_configure para habilitar consultas distribuidas ad hoc, en caso de que aún no esté habilitado.
CREATE PROC getBusinessLineHistory
AS
BEGIN
SELECT * FROM sys.databases
END
GO
sp_configure ''Show Advanced Options'', 1
GO
RECONFIGURE
GO
sp_configure ''Ad Hoc Distributed Queries'', 1
GO
RECONFIGURE
GO
SELECT * INTO #MyTempTable FROM OPENROWSET(''SQLNCLI'', ''Server=(local)/SQL2008;Trusted_Connection=yes;'',
''EXEC getBusinessLineHistory'')
SELECT * FROM #MyTempTable
Quassnoi me puso la mayor parte del camino allí, pero faltaba una cosa:
**** Necesitaba usar parámetros en el procedimiento almacenado. ****
Y OPENQUERY no permite que esto suceda:
¡Así que encontré una forma de trabajar con el sistema y no tengo que hacer que la definición de la tabla sea tan rígida y redefinirla dentro de otro procedimiento almacenado (y, por supuesto, tener la posibilidad de que se rompa)!
Sí, puede crear dinámicamente la definición de tabla devuelta desde el procedimiento almacenado mediante el uso de la instrucción OPENQUERY con variables falsas (siempre que NO HAY RESULTADO AJUSTE devuelva el mismo número de campos y en la misma posición que un conjunto de datos con buena información).
Una vez que se crea la tabla, puede utilizar el procedimiento almacenado exec en la tabla temporal durante todo el día.
Y para anotar (como se indicó anteriormente) debe habilitar el acceso a los datos,
EXEC sp_serveroption ''MYSERVERNAME'', ''DATA ACCESS'', TRUE
Código:
declare @locCompanyId varchar(8)
declare @locDateOne datetime
declare @locDateTwo datetime
set @locDateOne = ''2/11/2010''
set @locDateTwo = getdate()
--Build temporary table (based on bogus variable values)
--because we just want the table definition and
--since openquery does not allow variable definitions...
--I am going to use bogus variables to get the table defintion.
select * into #tempCoAttendanceRpt20100211
FROM OPENQUERY(DBASESERVER,
''EXEC DATABASE.dbo.Proc_MyStoredProc 1,"2/1/2010","2/15/2010 3:00 pm"'')
set @locCompanyId = ''7753231''
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
set @locCompanyId = ''9872231''
insert into #tempCoAttendanceRpt20100211
EXEC DATABASE.dbo.Proc_MyStoredProc @locCompanyId,@locDateOne,@locDateTwo
select * from #tempCoAttendanceRpt20100211
drop table #tempCoAttendanceRpt20100211
Gracias por la información que se proporcionó originalmente ... Sí, finalmente no tengo que crear todas estas definiciones de tablas falsas (estrictas) al usar datos de otro procedimiento almacenado o base de datos, y sí, también puede usar parámetros.
Buscar etiquetas de referencia:
Procedimiento almacenado de SQL 2005 en la tabla temporal
Openquery con procedimiento almacenado y variables 2005.
openquery con variables
ejecutar el procedimiento almacenado en la tabla temporal
Actualización: esto no funcionará con tablas temporales, por lo que tuve que recurrir a la creación manual de la tabla temporal.
Aviso de Bummer : esto no funcionará con tablas temporales , http://www.sommarskog.se/share_data.html#OPENQUERY
Referencia: Lo siguiente es definir LOCALSERVER. Puede parecer una palabra clave en el ejemplo, pero en realidad es solo un nombre. Así es como lo haces:
sp_addlinkedserver @server = ''LOCALSERVER'', @srvproduct = '''',
@provider = ''SQLOLEDB'', @datasrc = @@servername
Para crear un servidor vinculado, debe tener el permiso ALTER ANY SERVER, o ser miembro de cualquiera de los roles de servidor fijos sysadmin o setupadmin.
OPENQUERY abre una nueva conexión a SQL Server. Esto tiene algunas implicaciones:
El procedimiento al que llama con OPENQUERY no puede referir tablas temporales creadas en la conexión actual.
La nueva conexión tiene su propia base de datos predeterminada (definida con sp_addlinkedserver, la predeterminada es maestra), por lo que todas las especificaciones del objeto deben incluir un nombre de base de datos.
Si tiene una transacción abierta y mantiene bloqueos cuando llama a OPENQUERY, el procedimiento llamado no puede acceder a lo que bloquea. Es decir, si no tienes cuidado te bloquearás.
La conexión no es gratuita, por lo que hay una penalización de rendimiento.
Si desea hacerlo sin declarar primero la tabla temporal, puede intentar crear una función definida por el usuario en lugar de un procedimiento almacenado y hacer que esa función devuelva una tabla. Alternativamente, si desea utilizar el procedimiento almacenado, intente algo como esto:
CREATE TABLE #tmpBus
(
COL1 INT,
COL2 INT
)
INSERT INTO #tmpBus
Exec SpGetRecords ''Params''
Si el OPENROWSET le está causando problemas, hay otra manera de 2012 en adelante; haga uso de sys.dm_exec_describe_first_result_set_for_object, como se menciona aquí: ¿ Recuperar nombres de columna y tipos de un procedimiento almacenado?
Primero, cree este procedimiento almacenado para generar el SQL para el temporal
CREATE PROCEDURE dbo.usp_GetStoredProcTableDefinition(
@ProcedureName nvarchar(128),
@TableName nvarchar(128),
@SQL nvarchar(max) OUTPUT
)
AS
SET @SQL = ''CREATE TABLE '' + @tableName + '' (''
SELECT @SQL = @SQL + ''[''+name +''] ''+ system_type_name +'''' + '',''
FROM sys.dm_exec_describe_first_result_set_for_object
(
OBJECT_ID(@ProcedureName),
NULL
);
--Remove trailing comma
SET @SQL = SUBSTRING(@SQL,0,LEN(@SQL))
SET @SQL = @SQL +'')''
Para utilizar el procedimiento, llámelo de la siguiente manera:
DECLARE @SQL NVARCHAR(MAX)
exec dbo.usp_GetStoredProcTableDefinition
@ProcedureName=''dbo.usp_YourProcedure'',
@TableName=''##YourGlobalTempTable'',@SQL = @SQL OUTPUT
INSERT INTO ##YourGlobalTempTable
EXEC [dbo].usp_YourProcedure
select * from ##YourGlobalTempTable
Tenga en cuenta que estoy usando una tabla temporal global. Esto se debe a que el uso de EXEC para ejecutar el SQL dinámico crea su propia sesión, por lo que una tabla temporal ordinaria estaría fuera del alcance de cualquier código posterior. Si una tabla temporal global es un problema, puede usar una tabla temporal ordinaria, pero cualquier SQL posterior debería ser dinámico, es decir, también ejecutado por la declaración EXEC.
Si la consulta no contiene parámetros, use OpenQuery o use OpenRowset .
Lo básico sería crear un esquema según el procedimiento almacenado e insertarlo en esa tabla. p.ej:
DECLARE @abc TABLE(
RequisitionTypeSourceTypeID INT
, RequisitionTypeID INT
, RequisitionSourcingTypeID INT
, AutoDistOverride INT
, AllowManagerToWithdrawDistributedReq INT
, ResumeRequired INT
, WarnSupplierOnDNRReqSubmission INT
, MSPApprovalReqd INT
, EnableMSPSupplierCounterOffer INT
, RequireVendorToAcceptOffer INT
, UseCertification INT
, UseCompetency INT
, RequireRequisitionTemplate INT
, CreatedByID INT
, CreatedDate DATE
, ModifiedByID INT
, ModifiedDate DATE
, UseCandidateScheduledHours INT
, WeekEndingDayOfWeekID INT
, AllowAutoEnroll INT
)
INSERT INTO @abc
EXEC [dbo].[usp_MySp] 726,3
SELECT * FROM @abc
Si la tabla de resultados de su proceso almacenado es demasiado complicada para escribir manualmente la declaración "crear tabla" y no puede usar OPENQUERY O OPENROWSET, puede usar sp_help para generar la lista de columnas y tipos de datos para usted. Una vez que tenga la lista de columnas, solo tiene que formatearla para que se ajuste a sus necesidades.
Paso 1: Agregue "en #temp" a la consulta de salida (por ejemplo, "seleccione [...] en #temp desde [...]").
La forma más fácil es editar la consulta de salida en el proceso directamente. Si no puede cambiar el proceso almacenado, puede copiar el contenido en una nueva ventana de consulta y modificar la consulta allí.
Paso 2: Ejecutar sp_help en la tabla temporal. (por ejemplo, "exec tempdb..sp_help #temp")
Después de crear la tabla temporal, ejecute sp_help en la tabla temporal para obtener una lista de las columnas y los tipos de datos, incluido el tamaño de los campos varchar.
Paso 3: Copie los tipos y columnas de datos en una declaración de creación de tabla
Tengo una hoja de Excel que utilizo para formatear la salida de sp_help en una declaración "crear tabla". No necesita nada tan sofisticado, solo copie y pegue en su editor SQL. Utilice los nombres, tamaños y tipos de columna para crear una declaración "Crear tabla #x [...]" o "declarar tabla @x" que puede usar para INSERTAR los resultados del procedimiento almacenado.
Paso 4: Insertar en la tabla recién creada
Ahora tendrá una consulta que es como las otras soluciones descritas en este hilo.
DECLARE @t TABLE
(
--these columns were copied from sp_help
COL1 INT,
COL2 INT
)
INSERT INTO @t
Exec spMyProc
Esta técnica también se puede utilizar para convertir una tabla temporal ( #temp ) en una variable de tabla ( @temp ). Si bien esto puede ser más pasos que solo escribir la declaración de create table , evita errores manuales, como errores tipográficos y desajustes de tipos de datos en procesos grandes. Depurar un error tipográfico puede llevar más tiempo que escribir la consulta en primer lugar.
Si tienes la suerte de tener SQL 2012 o superior, puedes usar dm_exec_describe_first_result_set_for_object
Acabo de editar el sql proporcionado por gotqn. Gracias gotqn
Esto crea una tabla temporal global con el mismo nombre que el nombre del procedimiento. La tabla temporal se puede utilizar más tarde según sea necesario. Solo no olvides dejarlo caer antes de volver a ejecutar.
declare @procname nvarchar(255) = ''myProcedure'',
@sql nvarchar(max)
set @sql = ''create table ##'' + @procname + '' (''
begin
select @sql = @sql + ''['' + r.name + ''] '' + r.system_type_name + '',''
from sys.procedures AS p
cross apply sys.dm_exec_describe_first_result_set_for_object(p.object_id, 0) AS r
where p.name = @procname
set @sql = substring(@sql,1,len(@sql)-1) + '')''
execute (@sql)
execute(''insert ##'' + @procname + '' exec '' + @procname)
end
Código
CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 NCHAR(50) NOT NULL,
col3 TEXT NOT NULL,
col4 DATETIME NULL,
col5 NCHAR(50) NULL,
col6 CHAR(2) NULL,
col6 NCHAR(100) NULL,
col7 INT NULL,
col8 NCHAR(50) NULL,
col9 DATETIME NULL,
col10 DATETIME NULL
)
DECLARE @Para1 int
DECLARE @Para2 varchar(32)
DECLARE @Para3 varchar(100)
DECLARE @Para4 varchar(15)
DECLARE @Para5 varchar (12)
DECLARE @Para6 varchar(1)
DECLARE @Para7 varchar(1)
SET @Para1 = 1025
SET @Para2 = N''6as54fsd56f46sd4f65sd''
SET @Para3 = N''XXXX/UserName''
SET @Para4 = N''127.0.0.1''
SET @Para5 = N''XXXXXXX''
SET @Para6 = N''X''
SET @Para7 = N''X''
INSERT INTO #T1
(
col1,
col2,
col3,
col4,
col5,
col6,
col6,
col7,
col8,
col9,
col10,
)
EXEC [dbo].[usp_ProcedureName] @Para1, @Para2, @Para3, @Para4, @Para5, @Para6, @Para6
Espero que esto ayude. Por favor califique según corresponda.
Bueno, tiene que crear una tabla temporal, pero no tiene que tener el esquema correcto ... He creado un procedimiento almacenado que modifica una tabla temporal existente para que tenga las columnas necesarias con los datos correctos tipo y orden (eliminando todas las columnas existentes, agregando nuevas columnas):
GO
create procedure #TempTableForSP(@tableId int, @procedureId int)
as
begin
declare @tableName varchar(max) = (select name
from tempdb.sys.tables
where object_id = @tableId
);
declare @tsql nvarchar(max);
declare @tempId nvarchar(max) = newid();
set @tsql = ''
declare @drop nvarchar(max) = (select ''''alter table tempdb.dbo.'' + @tableName
+ '' drop column '''' + quotename(c.name) + '''';''''+ char(10)
from tempdb.sys.columns c
where c.object_id = '' +
cast(@tableId as varchar(max)) + ''
for xml path('''''''')
)
alter table tempdb.dbo.'' + @tableName + '' add '' + QUOTENAME(@tempId) + '' int;
exec sp_executeSQL @drop;
declare @add nvarchar(max) = (
select ''''alter table '' + @tableName
+ '' add '''' + name
+ '''' '''' + system_type_name
+ case when d.is_nullable=1 then '''' null '''' else '''''''' end
+ char(10)
from sys.dm_exec_describe_first_result_set_for_object(''
+ cast(@procedureId as varchar(max)) + '', 0) d
order by column_ordinal
for xml path(''''''''))
execute sp_executeSQL @add;
alter table '' + @tableName + '' drop column '' + quotename(@tempId) + '' '';
execute sp_executeSQL @tsql;
end
GO
create table #exampleTable (pk int);
declare @tableId int = object_Id(''tempdb..#exampleTable'')
declare @procedureId int = object_id(''examplestoredProcedure'')
exec #TempTableForSP @tableId, @procedureId;
insert into #exampleTable
exec examplestoredProcedure
Tenga en cuenta que esto no funcionará si sys.dm_exec_describe_first_result_set_for_object no puede determinar los resultados del procedimiento almacenado (por ejemplo, si utiliza una tabla temporal).
Si conoce los parámetros que se están pasando y si no tiene acceso para hacer sp_configure, edite el procedimiento almacenado con estos parámetros y el mismo se puede almacenar en una tabla global ##.
Yo haria lo siguiente
Cree (convierta SP a) un UDF (valor de tabla UDF).
select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory ''16 Mar 2009''
Encontré el mismo problema y esto es lo que hice para esto de la sugerencia de Paul . La parte principal aquí es usar NEWID()para evitar que varios usuarios ejecuten los procedimientos / scripts de la tienda al mismo tiempo, el dolor de la tabla temporal global.
DECLARE @sql varchar(max) = '''',
@tmp_global_table varchar(255) = ''##global_tmp_'' + CONVERT(varchar(36), NEWID())
SET @sql = @sql + ''select * into ['' + @tmp_global_table + ''] from YOURTABLE''
EXEC(@sql)
EXEC(''SELECT * FROM ['' + @tmp_global_table + '']'')
Esto se puede hacer en SQL Server 2014+ si el SP solo devuelve una tabla. Si alguien encuentra una forma de hacer esto para varias tablas, me encantaría saberlo.
DECLARE @storeProcname NVARCHAR(MAX) = ''''
SET @storeProcname = ''myStoredProc''
DECLARE @strSQL AS VARCHAR(MAX) = ''CREATE TABLE myTableName ''
SELECT @strSQL = @strSQL+STUFF((
SELECT '','' +name+'' '' + system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object (OBJECT_ID(@storeProcname),0)
FOR XML PATH('''')
),1,1,''('') + '')''
EXEC (@strSQL)
INSERT INTO myTableName
EXEC (''myStoredProc @param1=1, @param2=2'')
SELECT * FROM myTableName
DROP TABLE myTableName
Esto extrae la definición de la tabla devuelta de las tablas del sistema y la utiliza para crear la tabla temporal para usted. A continuación, puede rellenar desde el SP como se indicó anteriormente.
También hay variantes de esto que funcionan con SQL dinámico también.
Otro método es crear un tipo y usar PIPELINED para luego devolver su objeto. Esto se limita a conocer las columnas sin embargo. Pero tiene la ventaja de poder hacer:
SELECT *
FROM TABLE(CAST(f$my_functions(''8028767'') AS my_tab_type))
EXEC sp_serveroption ''YOURSERVERNAME'', ''DATA ACCESS'', TRUE
SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, ''EXEC db.schema.sproc 1'')
declare @temp table
(
name varchar(255),
field varchar(255),
filename varchar(255),
filegroup varchar(255),
size varchar(255),
maxsize varchar(255),
growth varchar(255),
usage varchar(255)
);
INSERT @temp Exec sp_helpfile;
select * from @temp;