una - tabla temporal sql server stored procedure




Insertar los resultados de un procedimiento almacenado en una tabla temporal (17)

¿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]')

  1. Estoy creando una tabla con el siguiente esquema y datos.
  2. Crear un procedimiento almacenado.
  3. 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] OFF
    

    VALORES (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;
    

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

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.


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 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 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.


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

  1. Cree (convierta SP a) un UDF (valor de tabla UDF).

  2. select * into #tmpBusLine from dbo.UDF_getBusinessLineHistory '16 Mar 2009'


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))

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;




stored-procedures