sql - एक अस्थायी तालिका में संग्रहीत प्रक्रिया के परिणाम डालें




sql-server sql-server-2005 (16)

सबसे आसान समाधान: 

CREATE TABLE #temp (...);

INSERT INTO #temp
EXEC [sproc];

यदि आप स्कीमा नहीं जानते हैं तो आप निम्न कार्य कर सकते हैं। कृपया ध्यान दें कि इस विधि में गंभीर सुरक्षा जोखिम हैं। 

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

मैं SELECT * INTO [temp table] FROM [stored procedure] एक SELECT * INTO [temp table] FROM [stored procedure] कैसे करूँ? FROM [Table] नहीं और [temp table] को परिभाषित किए बिना?

tmpBusLine से सभी डेटा का Select tmpBusLine ठीक काम करता है। 

select *
into tmpBusLine
from BusinessLine

मैं वही कोशिश कर रहा हूं, लेकिन stored procedure का उपयोग कर डेटा लौटाता हूं, यह बिल्कुल समान नहीं है। 

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

आउटपुट संदेश:

संदेश 156, स्तर 15, राज्य 1, रेखा 2 कीवर्ड 'exec' के पास गलत वाक्यविन्यास।

मैंने आउटपुट संग्रहीत प्रक्रिया के रूप में एक ही संरचना के साथ एक अस्थायी तालिका बनाने के कई उदाहरण पढ़े हैं, जो ठीक काम करता है, लेकिन किसी भी कॉलम की आपूर्ति करना अच्छा होगा।

  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

SQL सर्वर 2005 में आप एक तालिका में संग्रहीत प्रक्रिया के परिणाम डालने के लिए INSERT INTO ... EXEC का उपयोग कर सकते हैं। एमएसडीएन के INSERT दस्तावेज से (वास्तव में SQL सर्वर 2000 के लिए): 

--INSERT...EXECUTE procedure example
INSERT author_sales EXECUTE get_author_sales

आप इसके लिए 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

क्या आपकी संग्रहीत प्रक्रिया केवल डेटा पुनर्प्राप्त करती है या इसे संशोधित करती है? यदि इसका उपयोग केवल पुनर्प्राप्ति के लिए किया जाता है, तो आप संग्रहीत प्रक्रिया को फ़ंक्शन में परिवर्तित कर सकते हैं और सामान्य तालिका अभिव्यक्तियों (सीटीई) का उपयोग किए बिना इसे घोषित किए बिना कर सकते हैं: 

with temp as (
    select * from dbo.fnFunctionName(10, 20)
)
select col1, col2 from temp

हालांकि, सीटीई से जो कुछ भी हासिल किया जाना चाहिए, उसे केवल एक कथन में इस्तेमाल किया जाना चाहिए। आप with temp as ... एक with temp as ... नहीं कर सकते with temp as ... और SQL की कुछ पंक्तियों के बाद इसका उपयोग करने का प्रयास करें। अधिक जटिल प्रश्नों के लिए आपके पास एक कथन में एकाधिक सीटीई हो सकते हैं।

उदाहरण के लिए, 

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)

जब संग्रहीत प्रक्रिया बहुत सारे कॉलम लौटाती है और आप परिणाम को पकड़ने के लिए मैन्युअल रूप से "अस्थायी तालिका" बनाना नहीं चाहते हैं, तो मुझे संग्रहीत प्रक्रिया में जाने का सबसे आसान तरीका मिल गया है और "इन" खंड को जोड़ना है अंतिम चयन कथन और जहां खंड पर 1 = 0 जोड़ें।

संग्रहीत प्रक्रिया को एक बार चलाएं और वापस जाएं और आपके द्वारा अभी जोड़े गए SQL कोड को हटा दें। अब, आपके पास संग्रहित प्रक्रिया के परिणाम से मेल खाने वाली एक खाली तालिका होगी। आप एक अस्थायी तालिका के लिए या तो "स्क्रिप्ट टेबल बनाने के लिए" या बस उस तालिका में सीधे डालें।

यदि 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 को गतिशील होने की आवश्यकता होगी, जिसे EXEC कथन द्वारा भी निष्पादित किया जाता है।

यदि आप SQL 2012 या उच्चतर के लिए भाग्यशाली हैं, तो आप dm_exec_describe_first_result_set_for_object उपयोग कर सकते हैं

मैंने अभी gotqn द्वारा प्रदान की गई एसक्यूएल संपादित की है। धन्यवाद gotqn।

यह प्रक्रिया नाम के समान नाम के साथ एक वैश्विक अस्थायी तालिका बनाता है। बाद में अस्थायी तालिका का उपयोग किया जा सकता है। पुनः निष्पादन से पहले इसे छोड़ना न भूलें। 

    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

यदि आपकी संग्रहित प्रो की परिणाम तालिका हाथ से "तालिका बनाएं" कथन टाइप करने के लिए बहुत जटिल है, और आप OPENQUERY या OPENROWSET का उपयोग नहीं कर सकते हैं, तो आप कॉलम और डेटा प्रकारों की सूची जेनरेट करने के लिए sp_help का उपयोग कर सकते हैं। एक बार आपके पास कॉलम की सूची हो जाने के बाद, यह आपकी आवश्यकताओं के अनुसार इसे फ़ॉर्मेट करने का मामला है।

चरण 1: आउटपुट क्वेरी में "#temp" जोड़ें (उदाहरण के लिए "[...] में #temp में चुनें [...]")।

सबसे आसान तरीका सीधे proc में आउटपुट क्वेरी को संपादित करना है। अगर आप संग्रहीत प्रोसेस को नहीं बदल सकते हैं, तो आप सामग्री को एक नई क्वेरी विंडो में कॉपी कर सकते हैं और वहां क्वेरी को संशोधित कर सकते हैं।

चरण 2: temp तालिका पर sp_help चलाएं। (उदाहरण के लिए "exec tempdb..sp_help #temp")

अस्थायी तालिका बनाने के बाद, वर्चर्स फ़ील्ड के आकार सहित कॉलम और डेटा प्रकारों की एक सूची प्राप्त करने के लिए temp तालिका पर sp_help चलाएं।

चरण 3: डेटा कॉलम और प्रकारों को एक तालिका तालिका विवरण में कॉपी करें

मेरे पास एक एक्सेल शीट है जिसका उपयोग मैं sp_help के आउटपुट को "तालिका बनाएं" कथन में प्रारूपित करने के लिए करता हूं। आपको उस फैंसी की ज़रूरत नहीं है, बस अपने एसक्यूएल एडिटर में कॉपी और पेस्ट करें। "तालिका #x [...]" या "@x तालिका घोषित करें [...]" कथन बनाने के लिए कॉलम नाम, आकार और प्रकार का उपयोग करें, जिसे आप संग्रहीत प्रक्रिया के परिणामों को INSERT में उपयोग करने के लिए उपयोग कर सकते हैं।

चरण 4: नव निर्मित तालिका में डालें

अब आपके पास एक प्रश्न होगा जो इस धागे में वर्णित अन्य समाधानों की तरह है। 

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

INSERT INTO @t 
Exec spMyProc

इस तकनीक का उपयोग एक temp तालिका ( #temp ) को तालिका चर ( @temp ) में कनवर्ट करने के लिए भी किया जा सकता है। हालांकि यह स्वयं create table विवरण लिखने से अधिक कदम हो सकता है, यह बड़ी प्रक्रियाओं में टाइपोज़ और डेटा प्रकार के विसंगतियों जैसे मैन्युअल त्रुटि को रोकता है। एक टाइपो को डिबग करने से क्वेरी को पहले स्थान पर लिखने में अधिक समय लग सकता है।

यदि क्वेरी में पैरामीटर नहीं है, तो OpenQuery उपयोग 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

यह संग्रहीत proc नौकरी करता है: 

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 तालिका का उपयोग करने और बाद में इसे छोड़ने की आवश्यकता होगी।

कोड 

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

आशा है कि ये आपकी मदद करेगा। कृपया उपयुक्त के रूप में योग्यता प्राप्त करें।

I met the same problem and here is what I did for this from Paul's suggestion . The main part is here is to use NEWID() to avoid multiple users run the store procedures/scripts at the same time, the pain for global temporary table. 

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

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'

This can be done in SQL Server 2014+ provided SP only returns one table. If anyone finds a way of doing this for multiple tables I'd love to know about it. 

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

This pulls the definition of the returned table from system tables, and uses that to build the temp table for you. You can then populate it from the SP as stated before.

There are also variants of this that work with Dynamic SQL too. 

SELECT  *
INTO    #tmpTable
FROM    OPENQUERY(YOURSERVERNAME, 'EXEC test.dbo.prc_test 1')



sql   sql-server   sql-server-2005   tsql   stored-procedures  


stored-procedures