[sql] 저장 프로 시저의 결과를 임시 테이블에 삽입



11 Answers

임시 테이블을 먼저 선언하지 않고이를 수행하려는 경우, 스토어드 프로 시저가 아닌 사용자 정의 함수를 작성하여 해당 사용자 정의 함수가 테이블을 리턴하도록 할 수 있습니다. 또는 저장 프로 시저를 사용하려면 다음과 같이 해보십시오.

CREATE TABLE #tmpBus
(
   COL1 INT,
   COL2 INT
)

INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
Question

SELECT * INTO [temp table] FROM [stored procedure] 어떻게합니까? FROM [Table] 아니고 [temp table] 을 정의하지 않습니까?

BusinessLine 에서 모든 데이터를 Select 하여 tmpBusLine 작동합니다.

select *
into tmpBusLine
from BusinessLine

동일한 시도하고 있지만 데이터를 반환하는 stored procedure 를 사용하여 꽤 동일하지 않습니다.

select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'

출력 메시지 :

메시지 156, 수준 15, 상태 1, 줄 2 'exec'키워드 근처의 구문이 잘못되었습니다.

출력 저장 프로 시저와 동일한 구조로 임시 테이블을 만드는 몇 가지 예를 읽었지만 잘 작동하지만 저장 프로 시저를 제공하지 않는 것이 좋습니다.




저장된 proc의 결과 테이블이 너무 복잡하여 "create table"문을 직접 작성하지 않고 OPENQUERY 또는 OPENROWSET을 사용할 수없는 경우 sp_help를 사용하여 열 및 데이터 유형 목록을 생성 할 수 있습니다. 일단 열의 목록이 있으면 필요에 맞게 서식을 지정해야합니다.

1 단계 : 출력 쿼리에 "#temp"를 추가합니다 (예 : "[...]에서 #temp로 [...] 선택).

가장 쉬운 방법은 proc에서 직접 출력 쿼리를 편집하는 것입니다. 저장 프로 시저를 변경할 수 없으면 내용을 새 쿼리 창에 복사하고 그곳에서 쿼리를 수정할 수 있습니다.

2 단계 : 임시 테이블에서 sp_help를 실행합니다. (예 : "exec tempdb..sp_help #temp")

임시 테이블을 만든 후 temp 테이블에서 sp_help를 실행하여 varchar 필드의 크기를 비롯한 열과 데이터 형식의 목록을 가져옵니다.

3 단계 : create table 문에 데이터 열 및 유형 복사

sp_help의 출력을 "create table"문으로 포맷하는 데 사용하는 Excel 시트가 있습니다. 당신은 아무것도 복사 할 필요가 없으며 복사하여 SQL 편집기에 붙여 넣기 만하면됩니다. 열 이름, 크기 및 형식을 사용하여 저장 프로 시저의 결과를 INSERT하는 데 사용할 수있는 "Create table #x [...]"또는 "declare @x table [...]"문을 생성합니다.

4 단계 : 새로 생성 된 테이블에 삽입

이제이 스레드에서 설명하는 다른 솔루션과 같은 쿼리가 생성됩니다.

DECLARE @t TABLE 
(
   --these columns were copied from sp_help
   COL1 INT,
   COL2 INT   
)

INSERT INTO @t 
Exec spMyProc 

이 기술을 사용하여 임시 테이블 ( #temp )을 테이블 변수 ( @temp )로 변환 할 수도 있습니다. 이것은 create table 문을 직접 작성하는 것보다 더 많은 단계 일 수 있지만 큰 프로세스에서 오타 및 데이터 유형 불일치와 같은 수동 오류를 방지합니다. 오타를 디버깅하는 것은 처음부터 쿼리를 작성하는 것보다 시간이 더 걸릴 수 있습니다.




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;



SQL 2012 이상을 사용할 수 있다면 운이 좋으면 dm_exec_describe_first_result_set_for_object 를 사용할 수 있습니다 dm_exec_describe_first_result_set_for_object

나는 방금 gotqn에 의해 제공된 SQL을 편집했다. 고마워.

프로 시저 이름과 이름이 같은 전역 임시 테이블이 만들어집니다. 임시 테이블은 나중에 필요할 때 사용할 수 있습니다. 다시 실행하기 전에 삭제하는 것을 잊지 마십시오.

    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



OPENROWSET이 문제를 일으키는 경우 2012 년 이후로 다른 방법이 있습니다. 여기에 언급 된대로 sys.dm_exec_describe_first_result_set_for_object를 사용하십시오. 저장 프로 시저의 열 이름과 형식 검색?

먼저이 저장 프로 시저를 만들어 임시 SQL을 생성하십시오.

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

프로 시저를 사용하려면 다음과 같이 호출하십시오.

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

전역 임시 테이블을 사용하고 있습니다. EXEC를 사용하여 동적 SQL을 실행하면 자체 세션이 작성되므로 일반 임시 테이블이 모든 후속 코드의 범위를 벗어날 수 있습니다. 전역 임시 테이블에 문제가있는 경우 일반 임시 테이블을 사용할 있지만 이후 SQL은 동적이어야합니다. 즉 EXEC 문에서도 실행해야합니다.




가장 쉬운 솔루션 :

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

스키마를 모르는 경우 다음을 수행 할 수 있습니다. 이 방법에는 심각한 보안 위험이 있습니다.

SELECT * 
INTO #temp
FROM OPENROWSET('SQLNCLI', 
                'Server=localhost;Trusted_Connection=yes;', 
                'EXEC [db].[schema].[sproc]')



  1. 나는 다음과 같은 스키마와 데이터로 테이블을 생성하고있다.
  2. 저장 프로 시저를 만듭니다.
  3. 이제 프로 시저 결과가 무엇인지 알았으므로 다음 쿼리를 수행하고 있습니다.

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



I would do the following

  1. Create (convert SP to) a UDF (Table value UDF).

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




Another method is to create a type and use PIPELINED to then pass back your object. This is limited to knowing the columns however. But it has the advantage of being able to do:

SELECT * 
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))



암호

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

이게 도움이 되길 바란다. 적절하게 자격을 부여하십시오.




If you know the parameters that are being passed and if you don't have access to make sp_configure, then edit the stored procedure with these parameters and the same can be stored in a ##global table.




이것은 약간 수정 된 질문에 대한 답변입니다. 사용자 정의 함수에 대해 저장 프로 시저 사용을 포기할 수 있으면 인라인 테이블 반환 값 사용자 정의 함수를 사용할 수 있습니다. 이것은 본질적으로 테이블을 결과 집합으로 반환하는 저장 프로 시저 (매개 변수 사용)입니다. 그러므로 INTO 선언문과 잘 어울립니다.

여기에 좋은 기사 와 다른 사용자 정의 함수가 있습니다. 저장 프로 시저가 여전히 필요하다면 인라인 테이블 반환 값 사용자 정의 함수를 저장 프로 시저로 래핑 할 수 있습니다. 내장 프로 시저는 인라인 테이블 반환 사용자 정의 함수에서 select *를 호출 할 때 매개 변수를 전달합니다.

예를 들어, 특정 지역에 대한 고객 목록을 얻으려면 인라인 테이블 반환 사용자 정의 함수가 있습니다.

CREATE FUNCTION CustomersByRegion 
(  
    @RegionID int  
)
RETURNS TABLE 
AS
RETURN 
  SELECT *
  FROM customers
  WHERE RegionID = @RegionID
GO

그런 다음이 함수를 호출하여 결과에 대한 결과를 얻을 수 있습니다.

SELECT * FROM CustomersbyRegion(1)

또는 SELECT INTO를 수행하려면 다음과 같이하십시오.

SELECT * INTO CustList FROM CustomersbyRegion(1)

여전히 저장 프로 시저가 필요하면 다음과 같이 함수를 래핑하십시오.

CREATE PROCEDURE uspCustomersByRegion 
(  
    @regionID int  
)
AS
BEGIN
     SELECT * FROM CustomersbyRegion(@regionID);
END
GO

나는 이것이 원하는 결과를 얻기위한 가장 해킹이 덜한 방법이라고 생각한다. 추가 기능없이 기존 기능을 그대로 사용합니다. 내장 프로 시저에서 인라인 테이블 반환 사용자 정의 함수를 중첩하면 두 가지 방법으로 기능에 액세스 할 수 있습니다. 을 더한! 실제 SQL 코드에는 유지 보수 포인트가 하나뿐입니다.

OPENROWSET의 사용이 제안되었지만 이것은 OPENROWSET 함수가 (From Books Online)에 사용 된 것이 아닙니다.

OLE DB 데이터 원본의 원격 데이터에 액세스하는 데 필요한 모든 연결 정보를 포함합니다. 이 방법은 연결된 서버의 테이블에 액세스하는 대신에 OLE DB를 사용하여 원격 데이터에 연결하고 액세스하는 임시 방편입니다. OLE DB 데이터 소스에 대한 참조를 자주 사용하려면 연결된 서버를 대신 사용하십시오.

OPENROWSET을 사용하면 작업이 완료되지만 로컬 연결을 열어 데이터를 마샬링 할 때 약간의 추가 오버 헤드가 발생합니다. 또한 보안 위험을 초래하여 바람직하지 않을 수있는 특별 쿼리 권한이 필요하기 때문에 모든 경우에 옵션이 될 수 없습니다. 또한 OPENROWSET 방식은 둘 이상의 결과 집합을 반환하는 저장 프로 시저의 사용을 배제합니다. 하나의 저장 프로 시저에 여러 개의 인라인 테이블 값 사용자 정의 함수를 래핑하면이 작업을 수행 할 수 있습니다.



Related