질문자 :Ferdeen
SELECT * INTO [temp table] FROM [stored procedure] 어떻게 합니까? FROM [Table] 아니고 [temp table] 을 정의하지 않습니까?
BusinessLine 모든 데이터를 tmpBusLine Select 하면 제대로 작동합니다.
select * into tmpBusLine from BusinessLine 똑같이 시도하고 있지만 stored procedure 를 사용하는 것은 완전히 동일하지 않습니다.
select * into tmpBusLine from exec getBusinessLineHistory '16 Mar 2009'출력 메시지:
메시지 156, 수준 15, 상태 1, 줄 2 키워드 'exec' 근처에 잘못된 구문이 있습니다.
출력 저장 프로시저와 동일한 구조로 임시 테이블을 생성하는 몇 가지 예를 읽었습니다. 잘 작동하지만 열을 제공하지 않는 것이 좋습니다.
이를 위해 OPENROWSET 을 사용할 수 있습니다. 보세요. 또한 Ad Hoc Distributed Queries가 아직 활성화되어 있지 않은 경우를 대비하여 이를 활성화하는 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의 INSERT 문서 (실제로 SQL Server 2000의 경우):
--INSERT...EXECUTE procedure example INSERT author_sales EXECUTE get_author_sales이것은 귀하의 질문을 약간 수정한 버전에 대한 답변입니다. 사용자 정의 함수에 대한 저장 프로시저 사용을 포기할 수 있는 경우 인라인 테이블 반환 사용자 정의 함수를 사용할 수 있습니다. 이것은 본질적으로 테이블을 결과 집합으로 반환하는 저장 프로시저(매개변수 사용)입니다. 따라서 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 기능이 사용되도록 의도된 것이 아닙니다(온라인 설명서에서).
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" 절을 추가하는 것입니다. 마지막 select 문을 추가하고 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;저장된 프로시저의 결과 테이블이 너무 복잡하여 "테이블 생성" 문을 직접 입력할 수 없고 OPENQUERY 또는 OPENROWSET을 사용할 수 없는 경우 sp_help를 사용하여 열 및 데이터 형식 목록을 생성할 수 있습니다. 열 목록이 있으면 필요에 맞게 형식을 지정하기만 하면 됩니다.
1단계: 출력 쿼리에 "into #temp"를 추가합니다(예: "select [...] into #temp from [...]").
가장 쉬운 방법은 proc에서 출력 쿼리를 직접 편집하는 것입니다. 저장된 프로시저를 변경할 수 없는 경우 내용을 새 쿼리 창에 복사하고 거기에서 쿼리를 수정할 수 있습니다.
2단계: 임시 테이블에서 sp_help를 실행합니다. (예: "exec tempdb..sp_help #temp")
임시 테이블을 생성한 후 임시 테이블에서 sp_help를 실행하여 varchar 필드의 크기를 포함한 데이터 유형 및 열 목록을 가져옵니다.
3단계: 데이터 열 및 유형을 테이블 생성 문으로 복사
sp_help의 출력을 "테이블 생성" 문으로 형식화하는 데 사용하는 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 문을 직접 작성하는 것보다 더 많은 단계일 수 있지만 대규모 프로세스에서 오타 및 데이터 유형 불일치와 같은 수동 오류를 방지합니다. 오타를 디버깅하는 것은 처음에 쿼리를 작성하는 것보다 더 많은 시간이 걸릴 수 있습니다.
저장 프로시저는 데이터만 검색하거나 수정합니까? 검색에만 사용되는 경우 다음과 같이 저장 프로시저를 함수로 변환하고 선언하지 않고도 CTE(공통 테이블 식)를 사용할 수 있습니다.
with temp as ( select * from dbo.fnFunctionName(10, 20) ) select col1, col2 from temp 그러나 CTE에서 검색해야 하는 것은 무엇이든 하나의 명령문에서만 사용해야 합니다. with temp as ... 할 수 없으며 몇 줄의 SQL 후에 사용하려고 시도합니다. 더 복잡한 쿼리를 위해 하나의 문에 여러 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_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 문에 의해 실행되어야 합니다.
Quassnoi는 나를 거기에 가장 많이 넣었지만 한 가지 빠진 것이 있었습니다.
그래서 저는 시스템을 작동하는 방법을 찾았고 테이블 정의를 너무 엄격하게 만들 필요가 없으며 다른 저장 프로시저 내에서 재정의할 필요가 없습니다.
예, 가짜 변수가 있는 OPENQUERY 문을 사용하여 저장 프로시저에서 반환된 테이블 정의를 동적으로 만들 수 있습니다( NO RESULT 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
참고: 다음은 LOCALSERVER를 정의하는 것입니다. 예제에서는 키워드처럼 보일 수 있지만 실제로는 이름일 뿐입니다. 다음과 같이 하십시오.
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername연결된 서버를 만들려면 ALTER ANY SERVER 권한이 있거나 고정 서버 역할 sysadmin 또는 setupadmin의 구성원이어야 합니다.
OPENQUERY는 SQL Server에 대한 새 연결을 엽니다. 여기에는 다음과 같은 몇 가지 의미가 있습니다.
OPENQUERY로 호출하는 프로시저는 현재 연결에서 생성된 임시 테이블을 참조할 수 없습니다.
새 연결에는 자체 기본 데이터베이스(sp_addlinkedserver로 정의, 기본값은 master)가 있으므로 모든 개체 사양에는 데이터베이스 이름이 포함되어야 합니다.
열린 트랜잭션이 있고 OPENQUERY를 호출할 때 잠금을 보유하고 있는 경우 호출된 프로시저는 잠긴 항목에 액세스할 수 없습니다. 즉, 조심하지 않으면 자신을 차단하게 됩니다.
연결은 무료가 아니므로 성능 저하가 있습니다.
SQL 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 테이블을 사용하고 나중에 삭제해야 합니다.
저장 프로시저의 첫 번째 레코드 집합을 임시 테이블에 삽입하려면 다음을 알아야 합니다.
sp_executesql )을 실행하면 안 됩니다. 위의 내용은 제한 사항으로 보일 수 있지만 IMHO는 완벽하게 의미가 있습니다. sp_executesql 을 사용하는 경우 한 번은 두 개의 열을 반환하고 한 번은 10개의 열을 반환할 수 있으며 여러 결과 집합이 있는 경우 여러 테이블에도 삽입할 수 없습니다. 하나의 T-SQL 문에서 두 테이블의 최대값입니다( OUTPUT 절 사용 및 트리거 없음).
EXEC ... INTO ... 문을 수행하기 전에 임시 테이블 구조를 정의하는 방법입니다.
OBJECT_ID 와 함께 작동하는 반면 두 번째와 세 번째는 임시 쿼리에서도 작동합니다. CROSS APPLY 사용하고 동시에 여러 프로시저에 대한 임시 테이블 정의를 빌드할 수 있으므로 sp 대신 DMV를 사용하는 것을 선호합니다.
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이게 도움이 되길 바란다. 적절한 자격을 갖추십시오.
Passing Arrays/DataTables into Stored Procedures 를 찾았습니다. 이 문서는 문제를 해결하는 방법에 대한 또 다른 아이디어를 제공할 수 있습니다.
링크는 이미지 유형 매개변수를 사용하여 저장 프로시저에 전달할 것을 제안합니다. 그런 다음 저장 프로시저에서 이미지가 원본 데이터를 포함하는 테이블 변수로 변환됩니다.
임시 테이블과 함께 사용할 수 있는 방법이 있을 수 있습니다.
나는 같은 문제를 만났고 여기에 Paul의 제안 에서 내가 한 일이 있습니다. 주요 부분은 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 + ']')또 다른 방법은 유형을 만들고 PIPELINED를 사용하여 개체를 다시 전달하는 것입니다. 그러나 이것은 열을 아는 것으로 제한됩니다. 그러나 다음을 할 수 있다는 장점이 있습니다.
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에서도 작동하는 이것의 변형이 있습니다.
간단한 2단계 프로세스입니다. - 임시 테이블 만들기 - 임시 테이블에 삽입합니다.
동일한 작업을 수행하는 코드:
CREATE TABLE #tempTable (Column1 int, Column2 varchar(max)); INSERT INTO #tempTable EXEC [app].[Sproc_name] @param1 = 1, @param2 =2;주변을 검색한 후 특히 데이터베이스 관리자가 아닌 경우 저장 프로시저 결과 정의의 일반 스키마를 사용 OPENROWSET 또는 OPENQUERY 사용하지 않고 저장 프로시저에 대한 임시 테이블을 동적으로 생성하는 방법을 찾았습니다.
SQL 서버에는 모든 프로시저 결과 집합의 스키마를 제공할 수 있는 기본 제공 sp_describe_first_result_set 이 절차의 결과에서 스키마 테이블을 만들고 수동으로 모든 필드를 NULLABLE로 설정했습니다.
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 #dd 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 #dSQL Server 버전 - Microsoft SQL Server 2016(RTM) - 13.0.1601.5(빌드 17134:)에서 개발 및 테스트되었습니다.
사용 중인 SQL 서버 버전에 대한 스키마를 조정할 수 있습니다(필요한 경우).
전달되는 매개변수를 알고 있고 sp_configure를 만들 수 있는 액세스 권한이 없는 경우 이러한 매개변수를 사용하여 저장 프로시저를 편집하면 동일한 매개변수를 ##global 테이블에 저장할 수 있습니다.
질문에 몇 년이 늦었지만 빠르고 더러운 코드 생성을 위해 이와 같은 것이 필요했습니다. 다른 사람들이 말했듯이 임시 테이블을 미리 정의하는 것이 더 쉽다고 생각하지만 이 방법은 간단한 저장 프로시저 쿼리 또는 SQL 문에 대해 작동해야 합니다.
이것은 약간 복잡하지만 여기 기여자들과 DBA Stack Exchange Get stored procedure result column-types 의 Paul White 솔루션에서 차용합니다. 다시 말하지만, 이 접근 방식 및 예제는 다중 사용자 환경의 프로세스용으로 설계되지 않았습니다. 이 경우 테이블 정의는 코드 생성 템플릿 프로세스에서 참조하기 위해 전역 임시 테이블에 잠시 동안 설정됩니다.
나는 이것을 완전히 테스트하지 않았으므로주의 사항이있을 수 있으므로 Paul White의 답변에서 MSDN 링크로 이동하는 것이 좋습니다. 이는 SQL 2012 이상에 적용됩니다.
먼저 Oracle의 설명과 유사한 저장 프로시저 sp_describe_first_result_set을 사용합니다.
이렇게 하면 첫 번째 결과 집합의 첫 번째 행을 평가하므로 저장 프로시저 또는 문이 여러 쿼리를 반환하는 경우 첫 번째 결과만 설명합니다.
임시 테이블 정의를 생성하기 위해 선택할 단일 필드를 반환하는 작업을 분석하기 위해 저장 프로시저를 만들었습니다.
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 문제는 전역 테이블을 사용해야 하지만 충돌에 대한 걱정 없이 자주 삭제하고 생성할 수 있도록 충분히 고유하게 만들어야 한다는 것입니다.
예제에서 하이픈을 밑줄로 바꾸는 전역 변수에 Guid(FE264BF5_9C32_438F_8462_8A5DC8DEE49E)를 사용했습니다.
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다시 말하지만, 단순 저장 프로시저 쿼리와 단순 쿼리로만 테스트했기 때문에 마일리지가 다를 수 있습니다. 이것이 누군가를 돕기를 바랍니다.
다음은 매개변수가 있는 T-SQL입니다.
--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);글쎄, 당신은 임시 테이블을 생성해야하지만 올바른 스키마를 가질 필요는 없습니다....나는 기존 임시 테이블을 수정하여 올바른 데이터가 있는 필수 열을 갖도록 저장 프로시저를 만들었습니다. 유형 및 순서(기존 모든 열 삭제, 새 열 추가):
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_describe_first_result_set_for_object는 저장 프로 시저의 결과를 확인할 수없는 경우 작동하지 않습니다이 있습니다.
동적 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가 Dynamic 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); 
먼저 저장 프로시저를 수정하여 최종 결과를 임시 테이블에 저장합니다. 이렇게 하면 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 table2단계: 테이블에 레코드를 삽입할 SP를 아래와 같이 실행합니다.
Insert SP_Output_Table_1 EXE You_SP_Nane @Parameter1 = 52, @parameter2 =1출처 : http:www.stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table
| C#이 foreach에서 변수를 재사용하는 이유가 있습니까? (0) | 2022.01.08 |
|---|---|
| CSS 표시 속성의 전환 (0) | 2022.01.08 |
| 인스턴스의 클래스 이름을 가져오시겠습니까? (0) | 2022.01.06 |
| 배열의 경우 a[5] == 5[a]인 이유는 무엇입니까? (0) | 2022.01.06 |
| getter 및 setter/접근자를 사용하는 이유는 무엇입니까? (0) | 2022.01.06 |