임시 테이블에 저장 프로시저 결과 삽입
돼요?SELECT * INTO [temp table] FROM [stored procedure] 돼요? 안 돼요?FROM [Table]「」를 정의하지 않고,[temp table]
Select「」의 모든 BusinessLinetmpBusLine정상적으로 동작합니다.
select *
into tmpBusLine
from BusinessLine
도 같은 으로 하고 만, ★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★stored procedure데이터를 반환하는 것은 완전히 동일하지 않습니다.
select *
into tmpBusLine
from
exec getBusinessLineHistory '16 Mar 2009'
출력 메시지:
Msg 156, 레벨 15, 상태 1, 행 2 키워드 'exec' 근처의 구문이 잘못되었습니다.
출력 스토어 프로시저와 같은 구조의 임시 테이블을 작성하는 예는 몇 번 읽었지만, 열은 제공하지 않는 것이 좋습니다.
여기에는 OPENROWSET을 사용할 수 있습니다.보세요.애드혹 분산 쿼리를 활성화하기 위한 sp_configure 코드도 포함되어 있습니다(아직 활성화되지 않은 경우).
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
임시 테이블을 먼저 선언하지 않고 이 작업을 수행할 경우 저장 프로시저가 아닌 사용자 정의 함수를 생성하여 해당 사용자 정의 함수가 테이블을 반환하도록 할 수 있습니다.또는 스토어드 프로시저를 사용하는 경우는, 다음과 같은 조작을 실시합니다.
CREATE TABLE #tmpBus
(
COL1 INT,
COL2 INT
)
INSERT INTO #tmpBus
Exec SpGetRecords 'Params'
에서는 SQL Server 2005를 사용할 수 .INSERT INTO ... EXEC저장 프로시저의 결과를 표에 삽입합니다.MSDN 문서(실제로 SQL Server 2000의 경우)에서 다음 내용을 참조하십시오.
--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales
이것은 질문의 약간 수정된 버전에 대한 답변입니다.사용자 정의 함수에 스토어드 프로시저의 사용을 중지할 수 있는 경우 인라인 테이블 값의 사용자 정의 함수를 사용할 수 있습니다.이는 기본적으로 결과 집합으로 테이블을 반환하는 저장 프로시저(파라미터가 필요)이므로 INT 문과 함께 적절하게 배치됩니다.
다음은 이 기능과 기타 사용자 정의 함수에 대한 유용한 간단한 기사입니다.스토어드 프로시저가 필요한 경우 인라인 테이블 값 사용자 정의 함수를 스토어드 프로시저로 랩할 수 있습니다.스토어드 프로시저는 인라인 테이블 값 사용자 정의 함수에서 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 코드에 대한 유지 보수 포인트는 1개뿐입니다.
OPENROWSET의 사용이 권장되고 있지만, OPENROWSET 기능의 용도는 다음과 같습니다(From Books Online).
OLE DB 데이터 원본에서 원격 데이터에 액세스하는 데 필요한 모든 연결 정보를 포함합니다.이 방법은 링크된 서버의 테이블에 액세스하는 대신 OLE DB를 사용하여 원격 데이터를 연결하고 액세스하는 일회성 임시 방법입니다.OLE DB 데이터 원본에 대한 참조를 자주 하려면 연결된 서버를 대신 사용하십시오.
OPENROWSET을 사용하면 작업이 완료되지만 로컬 연결을 열고 데이터를 수집하기 위해 약간의 추가 오버헤드가 발생합니다.또한 보안 위험을 초래하는 애드혹쿼리 권한이 필요하기 때문에 모든 경우에 옵션이 될 수는 없습니다.또한 OPENROWSET 접근법은 두 개 이상의 결과 세트를 반환하는 저장 프로시저를 사용할 수 없게 됩니다.여러 인라인 테이블 값 사용자 정의 함수를 단일 저장 프로시저로 래핑하면 이를 달성할 수 있습니다.
Select @@ServerName
EXEC sp_serveroption @@ServerName, 'DATA ACCESS', TRUE
SELECT *
INTO #tmpTable
FROM OPENQUERY(YOURSERVERNAME, 'EXEC db.schema.sproc 1')
가장 쉬운 솔루션:
CREATE TABLE #temp (...); INSERT INTO #temp EXEC [sproc];
스키마를 모를 경우 다음을 수행할 수 있습니다.이 방법에는 심각한 보안 위험이 있습니다.
SELECT *
INTO #temp
FROM OPENROWSET('SQLNCLI',
'Server=localhost;Trusted_Connection=yes;',
'EXEC [db].[schema].[sproc]')
저장 프로시저가 많은 열을 반환하고 결과를 유지하기 위해 임시 테이블을 수동으로 "작성"하고 싶지 않은 경우, 가장 쉬운 방법은 저장 프로시저로 이동하여 마지막 선택 문에 "into" 절을 추가하고 where 절에 1=0을 추가하는 것입니다.
저장 프로시저를 한 번 실행하고 다시 돌아가서 방금 추가한 SQL 코드를 제거합니다.저장 프로시저의 결과와 일치하는 빈 테이블이 표시됩니다.임시 테이블에 대해 "스크립트 테이블을 작성"하거나 해당 테이블에 직접 삽입할 수 있습니다.
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;
저장된 proc의 결과 테이블이 너무 복잡하여 수동으로 "create table" 문을 입력할 수 없고 OPENQUERY 또는 OPENROWSET을 사용할 수 없는 경우 sp_help를 사용하여 열 및 데이터 유형 목록을 생성할 수 있습니다.열 목록을 가져오면 필요에 따라 열 형식을 지정하기만 하면 됩니다.
1단계: 출력 쿼리에 "into #temp"를 추가합니다(예: "select [...] to #temp from [...]).
가장 쉬운 방법은 proc에서 출력 쿼리를 직접 편집하는 것입니다.저장된 proc를 변경할 수 없는 경우 내용을 새 쿼리 창에 복사하고 거기서 쿼리를 수정할 수 있습니다.
순서 2: 임시 테이블에서 sp_help를 실행합니다.(예: exec tempdb..sp_help #help")
임시 테이블을 생성한 후 임시 테이블에서 sp_help를 실행하여 varchar 필드의 크기를 포함한 열 및 데이터 유형 목록을 가져옵니다.
스텝 3: 데이터 열과 유형을 create table 문에 복사합니다.
sp_help 출력의 형식을 "create table" 문으로 지정하는 데 사용하는 Excel 시트를 가지고 있습니다.이렇게 화려한 것은 필요 없습니다.복사하여 SQL 에디터에 붙여넣기만 하면 됩니다.열 이름, 크기 및 유형을 사용하여 "Create table #x [...]" 또는 "@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대규모 프로세스에서 오타나 데이터 타입 불일치 등의 수동 오류를 방지할 수 있습니다.오타를 디버깅하는 데 애초에 쿼리를 쓰는 것보다 시간이 더 걸릴 수 있습니다.
저장 프로시저는 데이터만 가져오거나 수정합니까?검색에만 사용되는 경우 다음과 같이 저장 프로시저를 함수로 변환하고 선언할 필요 없이 Common Table Expression(CTE; 공통 테이블 표현)을 사용할 수 있습니다.
with temp as (
select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp
가 있는 해야 합니다.CTE는 1개의 스테이트먼트에서만할 수 있습니다. 수 , 할 수 없다.with temp as ...SQL sql sql sql sql sql sql sql sql sql sql sql 。1개의 스테이트먼트에 복수의 CTE를 포함할 수 있어 보다 복잡한 쿼리를 실행할 수 있습니다.
예를들면,
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)
OPENROWSET으로 인해 문제가 발생하는 경우 2012년부터 다른 방법이 있습니다.여기서 설명한 바와 같이 sys.dm_exec_disc_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 문에 의해 실행되어야 합니다.
콰스누이가 날 거기까지 데려다 줬는데, 한 가지 빠진 게 있었어
****저장 프로시저에서 파라미터를 사용해야 했습니다.****
또한 OPENQUERY에서는 이러한 현상이 발생하지 않습니다.
그래서 시스템을 조작할 수 있는 방법을 찾았습니다.또한 테이블 정의를 너무 엄격하게 하지 않아도 됩니다.또, 다른 스토어드 프로시저내에서 재정의할 필요도 없습니다(물론, 망가질 수도 있습니다).
예, 가짜 변수와 함께 OPENQUERY 문을 사용하여 저장 프로시저에서 반환된 테이블 정의를 동적으로 만들 수 있습니다(NO RESUCT SET이 양호한 데이터를 가진 데이터 세트와 동일한 필드 수와 위치에 반환됨).
테이블이 생성되면 exec 스토어드 프로시저를 사용하여 하루 종일 임시 테이블에 넣을 수 있습니다.
또한 (위에서 설명한 바와 같이) 데이터 액세스를 활성화해야 합니다.
EXEC sp_serveroption 'MYSERVERNAME', 'DATA ACCESS', TRUE
코드:
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
처음에 알려준 정보 감사합니다...네, 마지막으로 다른 저장 프로시저 또는 데이터베이스의 데이터를 사용할 때 이러한 가짜(엄격한) 테이블 정의를 모두 작성할 필요가 없습니다.또한 파라미터도 사용할 수 있습니다.
참조 태그 검색:
SQL 2005 저장 프로시저를 임시 테이블에 저장
저장 프로시저 및 변수 2005를 사용한 오픈쿼리
변수가 있는 오픈 쿼리
저장 프로시저를 임시 테이블로 실행하다
업데이트: 임시 테이블에서는 사용할 수 없기 때문에 임시 테이블을 수동으로 작성해야 했습니다.
아쉬운 점: 임시 테이블에서는 작동하지 않습니다.http://www.sommarskog.se/share_data.html#OPENQUERY
레퍼런스:다음은 LOCAL SERVER를 정의하는 것입니다.이 예에서는 키워드처럼 보이지만 실제로는 이름일 뿐입니다.방법은 다음과 같습니다.
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
@provider = 'SQLOLEDB', @datasrc = @@servername
링크된 서버를 작성하려면 ALTER ANY SERVER 권한이 있거나 고정 서버 역할 sysadmin 또는 setupadmin의 구성원이어야 합니다.
OPENQUERY는 SQL Server에 대한 새 연결을 엽니다.여기에는 다음과 같은 영향이 있습니다.
OPENQUERY를 사용하여 호출하는 절차에서는 현재 연결에서 작성된 임시 테이블을 참조할 수 없습니다.
새 연결에는 자체 기본 데이터베이스(sp_addlinkedserver로 정의되고 기본값은 master)가 있으므로 모든 개체 지정에 데이터베이스 이름이 포함되어야 합니다.
열려 있는 트랜잭션이 있고 OPENQUERY를 호출할 때 잠금을 유지하고 있는 경우 호출된 프로시저는 잠근 항목에 액세스할 수 없습니다.즉, 조심하지 않으면 스스로 차단됩니다.
접속은 무료가 아니기 때문에 퍼포먼스 패널티가 발생합니다.
2012를 사용할 수 .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
이 저장된 프로시저가 작업을 수행합니다.
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
이것은 약간 수정한 것입니다.실제로 작동하도록 저장 프로시저 결과를 테이블에 삽입합니다.
하려면 , 「」를 .##GLOBAL이블테
저장 프로시저의 첫 번째 레코드 세트를 임시 테이블에 삽입하려면 다음 사항을 알아야 합니다.
- 저장 프로시저의 첫 번째 행 세트만 임시 테이블에 삽입할 수 있습니다.
- 스테이트먼트(T-SQL 스테이트먼트를 는 안 .
sp_executesql) - 먼저 임시 테이블의 구조를 정의해야 합니다.
수 . IMHO를 입니다.sp_executesql한 번에 두 개의 열과 열 하나를 반환할 수 있으며, 여러 개의 결과 세트가 있는 경우 여러 개의 테이블에도 삽입할 수 없습니다. 한 개의 T-SQL 문에서 두 개의 테이블에 최대값을 삽입할 수 있습니다(사용 방법).OUTPUT트리거 없음)을 지정합니다.
따라서 문제는 주로 어떻게 임시 테이블 구조를 정의하느냐입니다.EXEC ... INTO ...진술.
첫 번째는OBJECT_ID두 번째와 세 번째도 애드혹 쿼리와 함께 작동합니다.사용할 수 있는 SP 대신 DMV를 사용하는 것이 좋습니다.CROSS APPLY여러 절차를 동시에 수행하기 위한 임시 테이블 정의를 작성합니다.
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;
또, 다음의 점에 주의해 주세요.system_type_name매우 유용할 수 있습니다.열 전체 정의를 저장합니다.예를 들어 다음과 같습니다.
smalldatetime
nvarchar(max)
uniqueidentifier
nvarchar(1000)
real
smalldatetime
decimal(18,2)
대부분의 경우 직접 사용하여 테이블 정의를 생성할 수 있습니다.
따라서 대부분의 경우(스토어드 프로시저가 특정 기준에 부합하는 경우)에는 이러한 문제를 해결하기 위한 동적 스테이트먼트를 쉽게 작성할 수 있습니다(임시 테이블 작성, 스토어드 프로시저 결과 삽입, 데이터에 필요한 작업 수행).
위의 오브젝트는 다이내믹 T-SQL 문이 실행되거나 저장 프로시저에서 임시 테이블이 사용되는 경우와 같이 첫 번째 결과 세트 데이터를 정의하지 못합니다.
아래 스키마와 데이터로 테이블을 만듭니다.
저장 프로시저를 만듭니다.
이것으로 시술 결과를 알 수 있으므로 다음 쿼리를 수행합니다.
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 VALUES (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;
쿼리에 매개 변수가 포함되지 않으면OpenQuery그렇지 않으면 사용OpenRowset.
기본적으로 스토어드 프로시저에 따라 스키마를 생성하여 테이블에 삽입합니다.예를 들어 다음과 같습니다.
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
코드
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
이게 도움이 됐으면 좋겠어요.적절한 자격을 부여하십시오.
스토리지 프로시저로의 어레이/데이터 테이블 전달을 찾았습니다.이것에 의해서, 문제를 어떻게 해결할지에 대한 또 다른 아이디어를 얻을 수 있습니다.
링크에서는 이미지 유형 파라미터를 사용하여 저장 프로시저로 이동할 것을 권장합니다.그런 다음 저장 프로시저에서 영상이 원본 데이터를 포함하는 테이블 변수로 변환됩니다.
임시 테이블과 함께 사용할 수 있는 방법이 있을지도 모릅니다.
나는 같은 문제를 만났고 폴의 제안으로 이 문제를 해결했다.여기서의 주요 부분은NEWID()여러 사용자가 동시에 스토어 프로시저/프로시저를 실행하는 것을 방지하기 위해 글로벌 임시 테이블이 안고 있는 문제.
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 + ']')
또 다른 방법은 유형을 작성하고 PIPELINE을 사용하여 객체를 반환하는 것입니다.그러나 이는 열을 아는 것으로 제한됩니다.단, 다음과 같은 이점이 있습니다.
SELECT *
FROM TABLE(CAST(f$my_functions('8028767') AS my_tab_type))
이 작업은 저장 프로시저가 테이블을 하나만 반환하는 경우 SQL Server 2014+에서 수행할 수 있습니다.여러 테이블에 대해 이 방법을 찾는 사람이 있다면 알고 싶습니다.
DECLARE @storedProcname NVARCHAR(MAX) = ''
SET @storedProcname = '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(@storedProcname),0)
FOR XML PATH('')
),1,1,'(') + ')'
EXEC (@strSQL)
INSERT INTO myTableName
EXEC ('myStoredProc @param1=1, @param2=2')
SELECT * FROM myTableName
DROP TABLE myTableName
그러면 반환된 테이블의 정의가 시스템테이블에서 풀리고 이 정의를 사용하여 임시 테이블을 만듭니다.그런 다음 앞에서 설명한 대로 저장 프로시저에서 데이터를 채울 수 있습니다.
Dynamic SQL에서도 동작하는 변형도 있습니다.
을 생성할 수 있는 .OPENROWSET ★★★★★★★★★★★★★★★★★」OPENQUERY특히 데이터베이스 관리자가 아닌 경우 저장 프로시저의 결과 정의의 일반 스키마를 사용합니다.
proc SQL boot-in proc가 있습니다.sp_describe_first_result_set모든 절차 결과 세트의 스키마를 제공할 수 있습니다.하고, NULLABLE로 했습니다.LEE の le le le le le le le le le le 。
declare @procname varchar(100) = 'PROCEDURENAME' -- your procedure name
declare @param varchar(max) = '''2019-06-06''' -- your parameters
declare @execstr nvarchar(max) = N'exec ' + @procname
declare @qry nvarchar(max)
-- Schema table to store the result from sp_describe_first_result_set.
create table #d
(is_hidden bit NULL, column_ordinal int NULL, name sysname NULL, is_nullable bit NULL, system_type_id int NULL, system_type_name nvarchar(256) NULL,
max_length smallint NULL, precision tinyint NULL, scale tinyint NULL, collation_name sysname NULL, user_type_id int NULL, user_type_database sysname NULL,
user_type_schema sysname NULL,user_type_name sysname NULL,assembly_qualified_type_name nvarchar(4000),xml_collection_id int NULL,xml_collection_database sysname NULL,
xml_collection_schema sysname NULL,xml_collection_name sysname NULL,is_xml_document bit NULL,is_case_sensitive bit NULL,is_fixed_length_clr_type bit NULL,
source_server sysname NULL,source_database sysname NULL,source_schema sysname NULL,source_table sysname NULL,source_column sysname NULL,is_identity_column bit NULL,
is_part_of_unique_key bit NULL,is_updateable bit NULL,is_computed_column bit NULL,is_sparse_column_set bit NULL,ordinal_in_order_by_list smallint NULL,
order_by_list_length smallint NULL,order_by_is_descending smallint NULL,tds_type_id int NULL,tds_length int NULL,tds_collation_id int NULL,
tds_collation_sort_id tinyint NULL)
-- Get result set definition of your procedure
insert into #d
EXEC sp_describe_first_result_set @exestr, NULL, 0
-- Create a query to generate and populate a global temp table from above results
select
@qry = 'Create table ##t(' +
stuff(
(select ',' + name + ' '+ system_type_name + ' NULL'
from #d d For XML Path, TYPE)
.value(N'.[1]', N'nvarchar(max)')
, 1,1,'')
+ ')
insert into ##t
Exec '+@procname+' ' + @param
Exec sp_executesql @qry
-- Use below global temp table to query the data as you may
select * from ##t
-- **WARNING** Don't forget to drop the global temp table ##t.
--drop table ##t
drop table #d
SQL Server 버전 개발 및 테스트 - Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (빌드 17134:)
사용 중인 SQL 서버 버전에 맞게 스키마를 조정할 수 있습니다(필요한 경우).
간단한 2단계 프로세스로 - 임시 테이블 작성 - 임시 테이블에 삽입
동일한 작업을 수행하는 코드:
CREATE TABLE #tempTable (Column1 int, Column2 varchar(max));
INSERT INTO #tempTable
EXEC [app].[Sproc_name]
@param1 = 1,
@param2 =2;
전달되는 파라미터를 알고 있고 sp_configure에 액세스할 수 없는 경우 이러한 파라미터를 사용하여 스토어드 프로시저를 편집하면 동일한 파라미터를 ##global 테이블에 저장할 수 있습니다.
몇 년 늦었지만 빠르고 더러운 코드 생성을 위해 이런 게 필요했어요다른 사람들이 말한 것처럼 temp 테이블을 앞에 정의하는 것이 더 쉽다고 생각합니다만, 이 방법은 단순한 저장 프로시저 쿼리나 sql 문에 사용할 수 있습니다.
이것은 조금 복잡하지만, 여기의 기여자와 DBA Stack Exchange 저장 프로시저 결과 열 유형에서 Paul White의 솔루션을 차용합니다.다시 한 번 말씀드리지만, 이 접근법과 예는 다중 사용자 환경에서의 프로세스용으로 설계되어 있지 않습니다.이 경우 테이블 정의는 코드 생성 템플릿프로세스에서 참조하기 위해 글로벌 임시 테이블로 잠시 설정됩니다.
아직 충분히 테스트하지 않았기 때문에 주의사항이 있을 수 있으므로 Paul White의 답변에 있는 MSDN 링크로 이동해 주십시오.이는 SQL 2012 이상에 적용됩니다.
먼저 Oracle의 설명과 유사한 저장 프로시저 sp_describe_first_result_set을 사용합니다.
이렇게 하면 첫 번째 결과 세트의 첫 번째 행이 평가되므로 저장 프로시저 또는 문이 여러 쿼리를 반환할 경우 첫 번째 결과만 설명합니다.
stored proc를 생성하여 선택할 수 있는 단일 필드를 반환하는 작업을 세분화하여 임시 테이블 정의를 만듭니다.
CREATE OR ALTER PROCEDURE [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
(
@sql NVARCHAR(4000)
,@table_name VARCHAR(100)
,@TableDefinition NVARCHAR(MAX) OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @TempTableDefinition NVARCHAR(MAX)
DECLARE @NewLine NVARCHAR(4) = CHAR(13)+CHAR(10)
DECLARE @ResultDefinition TABLE ( --The View Definition per MSDN
is_hidden bit NOT NULL
, column_ordinal int NOT NULL
, [name] sysname NULL
, is_nullable bit NOT NULL
, system_type_id int NOT NULL
, system_type_name nvarchar(256) NULL
, max_length smallint NOT NULL
, [precision] tinyint NOT NULL
, scale tinyint NOT NULL
, collation_name sysname NULL
, user_type_id int NULL
, user_type_database sysname NULL
, user_type_schema sysname NULL
, user_type_name sysname NULL
, assembly_qualified_type_name nvarchar(4000)
, xml_collection_id int NULL
, xml_collection_database sysname NULL
, xml_collection_schema sysname NULL
, xml_collection_name sysname NULL
, is_xml_document bit NOT NULL
, is_case_sensitive bit NOT NULL
, is_fixed_length_clr_type bit NOT NULL
, source_server sysname NULL
, source_database sysname NULL
, source_schema sysname NULL
, source_table sysname NULL
, source_column sysname NULL
, is_identity_column bit NULL
, is_part_of_unique_key bit NULL
, is_updateable bit NULL
, is_computed_column bit NULL
, is_sparse_column_set bit NULL
, ordinal_in_order_by_list smallint NULL
, order_by_is_descending smallint NULL
, order_by_list_length smallint NULL
, tds_type_id int NOT NULL
, tds_length int NOT NULL
, tds_collation_id int NULL
, tds_collation_sort_id tinyint NULL
)
--Insert the description into table variable
INSERT @ResultDefinition
EXEC sp_describe_first_result_set @sql
--Now Build the string to create the table via union select statement
;WITH STMT AS (
SELECT N'CREATE TABLE ' + @table_name + N' (' AS TextVal
UNION ALL
SELECT
CONCAT(
CASE column_ordinal
WHEN 1 THEN ' ' ELSE ' , ' END --Determines if comma should precede
, QUOTENAME([name]) , ' ', system_type_name -- Column Name and SQL TYPE
,CASE is_nullable
WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END --NULLABLE CONSTRAINT
) AS TextVal
FROM @ResultDefinition WHERE is_hidden = 0 -- May not be needed
UNION ALL
SELECT N');' + @NewLine
)
--Now Combine the rows to a single String
SELECT @TempTableDefinition = COALESCE (@TempTableDefinition + @NewLine + TextVal, TextVal) FROM STMT
SELECT @TableDefinition = @TempTableDefinition
END
문제는 글로벌 테이블을 사용해야 하지만 충돌을 걱정하지 않고 자주 드롭하고 작성할 수 있도록 충분히 고유하게 만들어야 한다는 것입니다.
로 대체한 FE264BF5_9C32_438F_8462_8A5DC8DE49E를 사용했습니다.
DECLARE @sql NVARCHAR(4000) = N'SELECT @@SERVERNAME as ServerName, GETDATE() AS Today;'
DECLARE @GlobalTempTable VARCHAR(100) = N'##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable'
--@sql can be a stored procedure name like dbo.foo without parameters
DECLARE @TableDef NVARCHAR(MAX)
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC [dbo].[sp_GetTableDefinitionFromSqlBatch_DescribeFirstResultSet]
@sql, @GlobalTempTable, @TableDef OUTPUT
--Creates the global table ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @TableDef
--Now Call the stored procedure, SQL Statement with Params etc.
INSERT ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
EXEC sp_executesql @sql
--Select the results into your undefined Temp Table from the Global Table
SELECT *
INTO #MyTempTable
FROM ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
SELECT * FROM #MyTempTable
DROP TABLE IF EXISTS #MyTempTable
DROP TABLE IF EXISTS ##FE264BF5_9C32_438F_8462_8A5DC8DEE49E_MyTempTable
이번에도 간단한 스토어드 프로시저 문의와 간단한 문의만으로 테스트했으므로 주행거리가 달라질 수 있습니다.이게 도움이 됐으면 좋겠네요.
가 있는 .
--require one time execution if not configured before
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
--require one time execution if not configured before
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
--the query
DECLARE @param1 int = 1, @param2 int = 2
DECLARE @SQLStr varchar(max) = 'SELECT * INTO #MyTempTable
FROM OPENROWSET(''SQLNCLI'',
''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
''exec StoredProcedureName '+ CAST(@param1 AS varchar(15)) +','+ CAST(@param2 AS varchar(15)) +''') AS a ;
select * from #MyTempTable;
drop table #MyTempTable
';
EXECUTE(@SQLStr);
동적 SQL에서 임시 테이블을 생성하도록 하면 저장 프로시저를 호출하는 연결이 아닌 동적 SQL 연결이 이 테이블을 소유합니다.
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
SELECT * FROM #Pivoted;
메시지 208, 레벨 16, 상태 0 개체 이름 '#Pivoted'가 잘못되었습니다.이는 #Pivoted가 동적 SQL 연결에 의해 소유되기 때문입니다.그래서 마지막 지시는
SELECT * FROM #Pivoted
에러가 발생.
이 문제에 직면하지 않는 한 가지 방법은 #Pivoted에 대한 모든 참조가 동적 쿼리 자체 내부에서 이루어지도록 하는 것입니다.
DECLARE @COMMA_SEPARATED_KEYS varchar(MAX);
DROP TABLE IF EXISTS KV;
CREATE TABLE KV (id_person int, mykey varchar(30), myvalue int);
INSERT INTO KV VALUES
(1, 'age', 16),
(1, 'weight', 63),
(1, 'height', 175),
(2, 'age', 26),
(2, 'weight', 83),
(2, 'height', 185);
WITH cte(mykey) AS (
SELECT DISTINCT mykey FROM KV
)
SELECT @COMMA_SEPARATED_KEYS=STRING_AGG(mykey,',') FROM cte;
SELECT @COMMA_SEPARATED_KEYS AS keys;
DECLARE @ExecuteExpression varchar(MAX);
DROP TABLE IF EXISTS #Pivoted;
SET @ExecuteExpression = N'
SELECT *
INTO #Pivoted
FROM
(
SELECT
mykey,
myvalue,
id_person
FROM KV
) AS t
PIVOT(
MAX(t.myvalue)
FOR mykey IN (COMMA_SEPARATED_KEYS)
) AS pivot_table;
SELECT * FROM #Pivoted;
';
SET @ExecuteExpression = REPLACE(@ExecuteExpression, 'COMMA_SEPARATED_KEYS', @COMMA_SEPARATED_KEYS);
EXEC(@ExecuteExpression);
임시 테이블을 만들 필요는 있지만 올바른 스키마를 가질 필요는 없습니다.기존 임시 테이블이 올바른 데이터 유형 및 순서를 가진 필수 열을 갖도록 수정하는 저장 프로시저를 만들었습니다(기존 열을 모두 삭제하고 새 열을 추가).
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
sys.dm_exec_first_result_set_for_object가 저장 프로시저의 결과를 판별할 수 없는 경우(예를 들어 임시 테이블을 사용하는 경우)에는 동작하지 않습니다.
먼저 저장 프로시저를 수정하여 최종 결과를 임시 테이블에 저장합니다.이를 통해 SP 출력 필드와 일치하는 테이블을 만듭니다.그런 다음 select 문을 사용하여 해당 임시 테이블을 임의의 테이블 이름에 저장합니다.그런 다음 2단계에서 설명한 대로 SP를 실행합니다.
1단계: 저장 프로시저를 수정하여 최종 결과를 임시 테이블에 저장합니다.
[your stored procedure]
into #table_temp //this will insert the data to a temp table
from #table_temp
select * into SP_Output_Table_1 from #table_temp //this will save data to a actual table
2단계: 다음과 같이 SP를 실행하여 테이블에 레코드를 삽입합니다.
Insert SP_Output_Table_1
EXE You_SP_Nane @Parameter1 = 52, @parameter2 =1
언급URL : https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
'itsource' 카테고리의 다른 글
| Count()에서 조건을 지정할 수 있습니까? (0) | 2023.04.07 |
|---|---|
| SQL Server 2008 Express에서 동일한 서버에 SQL Server 데이터베이스를 복제하려면 어떻게 해야 합니까? (0) | 2023.04.07 |
| WordPress 사용자 정의 기능 만들기 (0) | 2023.04.02 |
| MongoDB가 작동하지 않는다."오류: dbpath(/data/db)가 존재하지 않습니다." (0) | 2023.04.02 |
| 스트라이프 오류: '토큰이 지원되지 않습니다' 구독에 등록하려고 하면 (0) | 2023.04.02 |