none
How to SELECT * INTO [temp table] FROM [Stored Procedure]

    问题

  • I have a below SP,

    CREATE PROCEDURE [dbo].[TEST_SP]
       @StrXML XML
    AS
    BEGIN TRY
    
    	SET NOCOUNT ON
    
    			CREATE TABLE #tXML (InputXML XML)
    			INSERT INTO #tXML (InputXML) SELECT @StrXML
    			
    			DECLARE @Cols NVARCHAR(MAX),
    					@SQL NVARCHAR(MAX)
    
    			SET @Cols = 
    			STUFF(
    			(
    			SELECT ', MAX(CASE WHEN [Attribute]=' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)'),CHAR(39)) +
    			' THEN [Attribute_Value] ELSE NULL END) AS ' + QUOTENAME(attributes.value('local-name(.)','VARCHAR(25)')) + CHAR(13)
    			FROM #tXML t
    			CROSS APPLY InputXML.nodes('/PDI_Approval_Message/Transactions/Transaction/Attributes/*')Attributes (attributes)
    			FOR XML PATH(''),TYPE
    			).value('.','NVARCHAR(MAX)'),1,1,'')
    			
    			SET @SQL = N'
    			SELECT 
    				SourceSystemName,
    				SourceSystemValue, 
    				TransactionTypeName,
    				TransactionTypeValue,
    				RequestTypeName,
    				RequestTypeValue,
    				SubmittedOnName,
    				SubmittedOnValue,
    				SubmittedByName,
    				SubmittedByValue,
    				' + CHAR(13) + @Cols + '
    				INTO ##TEMP
    			FROM(
    			SELECT 
    				header.value(''./SourceSystem[1]/@Name'',''VARCHAR(15)'') AS SourceSystemName,
    				header.value(''./SourceSystem[1]/@Value'',''VARCHAR(15)'') AS SourceSystemValue,
    				header.value(''./TransactionType[1]/@Name'',''VARCHAR(15)'') AS TransactionTypeName,
    				header.value(''./TransactionType[1]/@Value'',''VARCHAR(15)'') AS TransactionTypeValue,
    				header.value(''./RequestType[1]/@Name'',''VARCHAR(15)'') AS RequestTypeName,
    				header.value(''./RequestType[1]/@Value'',''VARCHAR(15)'') AS RequestTypeValue,
    				header.value(''./SubmittedOn[1]/@Name'',''VARCHAR(15)'') AS SubmittedOnName,
    				header.value(''./SubmittedOn[1]/@Value'',''VARCHAR(15)'') AS SubmittedOnValue,
    				header.value(''./SubmittedBy[1]/@Name'',''VARCHAR(15)'') AS SubmittedByName,
    				header.value(''./SubmittedBy[1]/@Value'',''VARCHAR(15)'') AS SubmittedByValue,
    				attributes.value(''local-name(.)'',''VARCHAR(25)'') Attribute,
    				attributes.value(''.'',''VARCHAR(15)'') AS Attribute_Value
    				FROM #tXML
    				CROSS APPLY InputXML.nodes(''/PDI_Approval_Message/Header'')Header(header)
    				CROSS APPLY InputXML.nodes(''/PDI_Approval_Message/Transactions/Transaction/Attributes/*'')Attributes (attributes)
    			) AS InputXML
    			GROUP BY 
    				SourceSystemName,
    				SourceSystemValue,
    				TransactionTypeName,
    				TransactionTypeValue,
    				RequestTypeName,
    				RequestTypeValue,
    				SubmittedOnName,
    				SubmittedOnValue,
    				SubmittedByName,
    				SubmittedByValue'
    
    			EXEC SP_EXECUTESQL @SQL 
    			SELECT * FROM ##TEMP
    
    			DROP TABLE #tXML
    			DROP TABLE ##TEMP
    
    
       SET NOCOUNT OFF
    
    END TRY
    
    BEGIN CATCH
    
    			
    END CATCH
    
    DECLARE @StrXML XML
    SET @StrXML = '<PDI_Approval_Message>
     <Header>
      <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
      <TransactionType Value="Timesheet" Name="WF TS"></TransactionType>
      <RequestType Value="Submit" Name="Action"></RequestType>
      <SubmittedOn Value="02/06/2010" Name=""></SubmittedOn>
      <SubmittedBy Value="AYAZ" Name="hannok"></SubmittedBy>
     </Header>
    
     <Transactions>
      <Transaction>
       <Attributes>
        <WF_Company_ID>100</WF_Company_ID>
        <WF_Employee_ID>625</WF_Employee_ID>
        <WF_Position>Clerk_D</WF_Position>
        <WF_TimeSheet_ID>1234</WF_TimeSheet_ID>
        <WF_Week_Ending>2010-07-05 1:59:00</WF_Week_Ending>
        <WF_Site_ID>34</WF_Site_ID>
        <WF_Regular_Hrs>35</WF_Regular_Hrs>
        <WF_Overtime_Hrs>5</WF_Overtime_Hrs>
       </Attributes>
      </Transaction>
     </Transactions>
    </PDI_Approval_Message>'
    EXEC AS_InsSubmitTransaction_SP @StrXML, 'Timesheet', 0
    

    Now I need to use this SP data in another SP where I need to insert all data into TEMP table, but the limitation is that we can't define table defination...I need same data struture as per  calling SP...

    Please help me

    2010年8月23日 11:30

答案

全部回复

  • If your stored procedure is "well-behaved", the following will work to make sproc results table-like:

    SELECT * INTO #spWho
    FROM OPENQUERY(localhost,'exec sp_who')
    

    Related links:

    http://sqlusa.com/bestpractices2005/selectfromsproc/

    http://sqlusa.com/bestpractices2005/createemptytable/


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM


    2010年8月23日 11:47
  • See this article on my web site for a couple of alternatives:
    http://www.sommarskog.se/share_data.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    2010年8月23日 12:57
  • executing (exec sp_who2) gives me different row set & executing with openquery() as per SQLUSA above gives me result set less columns.

    Only spid, ecid, status, loginname, hostname, blk, dbname, cmd & request_id

     

    Try this:

    create table #tempTable (....)

    insert into #tempTable

    exec sp_who2

     

    2010年8月23日 13:21
  • executing (exec sp_who2) gives me different row set & executing with openquery() as per SQLUSA above gives me result set less columns.

     


    The "brilliant" designer of sp_who2 duplicated the SPID column in violation of one of the basic tenets of RDBMS science. Therefore, SELECT INTO OPENQUERY will fail.

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    2010年8月23日 14:05
  • the limitation is that..we can't create temp table with column defination as my above procedure will return different set of result based on  different condition...

    Is there any other way without "OPENQUERY"

    2010年8月23日 17:01
  • i did below to use OPENQUERY ,

    Register link server like below,

    sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '',
              @provider = 'SQLOLEDB', @datasrc = @@servername
    

    Then I wrote below SP to call,

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    CREATE PROCEDURE [dbo].[AS_Call_SP]
       @StrXML XML,
    	 @TransactionName VARCHAR(50),
    	 @ErrCode INT OUTPUT
    AS
    BEGIN TRY
    			SELECT * INTO #spWho
    			FROM OPENQUERY(LOCALSERVER,'EXEC TEST_SP @StrXML')
    END TRY
    
    BEGIN CATCH
    
    END CATCH
    
    

    But, this throws me error, what is missing?

    OLE DB provider "SQLNCLI" for linked server "LOCALSERVER" returned message "Deferred prepare could not be completed.".

    Msg 8180, Level 16, State 1, Line 1

    Statement(s) could not be prepared.

    Msg 137, Level 15, State 2, Line 1

    Must declare the scalar variable "@StrXML".

     

     

    2010年8月23日 17:04
  • If you turn the stored procedure into a table-valued function you could then do this:

    SELECT * INTO #tempTable
    FROM
    fn_test_sp(@strXML)
    
    2010年8月23日 18:11
  • > the limitation is that..we can't create temp table with column defination as my above procedure will return different set of result based on  different condition...

    It does not seem to me like that from the code you posted.

    But if it is really that bad, why mess with T-SQL stored procedures at all? T-SQL was not designed for this venture. Do this in a stored procedure in the CLR instead, and where you can use the XML classes in .Net Framework to extract the data, rather than XQuery, which is quite an uninfinished component in SQL Server.

    Or do it all client-side. Where is the XML document coming from?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    2010年8月23日 19:10
  • >i did below to use OPENQUERY ,

    >    SELECT * INTO #spWho

    You need to use permanent or ##globaltemporary table for scope outside the sproc.

    Checkout the uspGetBOM sample sproc at the following link:

    http://sqlusa.com/bestpractices2005/selectfromsproc/

     


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    2010年8月23日 20:20
  • thank you all
    2010年8月23日 22:15
  • but I think in function we need to give table defintion..what say?
    2010年8月23日 22:18
  • resultret are different based on XML...the XML attributes are varying and this comes from client side....and I want to use above piece of code again and again..that's why I decide to made a separate SP to call again and again..anyway thanks for all of your input...I will think to do in other way
    2010年8月23日 22:21
  • I got the following message when running a similer query. any ideas how I should configure the server?

    Server 'TESTSERVER' is not configured for DATA ACCESS.

    2011年11月29日 19:39
  • From the second SP you can do the following:

    Declare @tblValues TABLE(Col1 INT, Col2 VARCHAR(50)...) - This table should match the columns being returned by SP1

    Then INSERT the values of SP1 into @tblValues

    INSERT INTO @tblValues

    EXEC SP1

    You can now use the values returned as

    SELECT * FROM @tblValues

    2012年7月6日 14:26
  • There is mistake in your XML query.  You copy the XML into a table, then present two copies of the column for CROSS APPLY.  You can simply query the XML directly without inserting into a temp table and use the .nodes method to drill down from the same bit of XML.  Hopefully my example below should demonstrate this.  When I ran these two queries on my laptop, the Actual Execution Plan showed query cost 94% for the old query, and 6% for the new one:

    DECLARE @StrXML XML
    SET @StrXML = '<PDI_Approval_Message>
     <Header>
      <SourceSystem Value="PDI/Workforce" Name="WF"></SourceSystem>
      <TransactionType Value="Timesheet" Name="WF TS"></TransactionType>
      <RequestType Value="Submit" Name="Action"></RequestType>
      <SubmittedOn Value="02/06/2010" Name=""></SubmittedOn>
      <SubmittedBy Value="AYAZ" Name="hannok"></SubmittedBy>
     </Header>
    
     <Transactions>
      <Transaction>
       <Attributes>
        <WF_Company_ID>100</WF_Company_ID>
        <WF_Employee_ID>625</WF_Employee_ID>
        <WF_Position>Clerk_D</WF_Position>
        <WF_TimeSheet_ID>1234</WF_TimeSheet_ID>
        <WF_Week_Ending>2010-07-05 1:59:00</WF_Week_Ending>
        <WF_Site_ID>34</WF_Site_ID>
        <WF_Regular_Hrs>35</WF_Regular_Hrs>
        <WF_Overtime_Hrs>5</WF_Overtime_Hrs>
       </Attributes>
      </Transaction>
     </Transactions>
    </PDI_Approval_Message>'
    
    SELECT
    	header.value('./SourceSystem[1]/@Name','VARCHAR(15)') AS SourceSystemName,
    	header.value('./SourceSystem[1]/@Value','VARCHAR(15)') AS SourceSystemValue,
    	header.value('./TransactionType[1]/@Name','VARCHAR(15)') AS TransactionTypeName,
    	header.value('./TransactionType[1]/@Value','VARCHAR(15)') AS TransactionTypeValue,
    	header.value('./RequestType[1]/@Name','VARCHAR(15)') AS RequestTypeName,
    	header.value('./RequestType[1]/@Value','VARCHAR(15)') AS RequestTypeValue,
    	header.value('./SubmittedOn[1]/@Name','VARCHAR(15)') AS SubmittedOnName,
    	header.value('./SubmittedOn[1]/@Value','VARCHAR(15)') AS SubmittedOnValue,
    	header.value('./SubmittedBy[1]/@Name','VARCHAR(15)') AS SubmittedByName,
    	header.value('./SubmittedBy[1]/@Value','VARCHAR(15)') AS SubmittedByValue,
    	attributes.value('local-name(.)','VARCHAR(25)') Attribute,
    	attributes.value('.','VARCHAR(15)') AS Attribute_Value
    FROM @StrXML.nodes('.') x(InputXML)
    	CROSS APPLY InputXML.nodes('/PDI_Approval_Message/Header')Header(header)
    	CROSS APPLY InputXML.nodes('/PDI_Approval_Message/Transactions/Transaction/Attributes/*')Attributes (attributes)
    
    
    SELECT
    	header.value('(SourceSystem/@Name)[1]','VARCHAR(15)') AS SourceSystemName,
    	header.value('(SourceSystem/@Value)[1]','VARCHAR(15)') AS SourceSystemValue,
    	header.value('(TransactionType/@Name)[1]','VARCHAR(15)') AS TransactionTypeName,
    	header.value('(TransactionType/@Value)[1]','VARCHAR(15)') AS TransactionTypeValue,
    	header.value('(RequestType/@Name)[1]','VARCHAR(15)') AS RequestTypeName,
    	header.value('(RequestType/@Value)[1]','VARCHAR(15)') AS RequestTypeValue,
    	header.value('(SubmittedOn/@Name)[1]','VARCHAR(15)') AS SubmittedOnName,
    	header.value('(SubmittedOn/@Value)[1]','VARCHAR(15)') AS SubmittedOnValue,
    	header.value('(SubmittedBy/@Name)[1]','VARCHAR(15)') AS SubmittedByName,
    	header.value('(SubmittedBy/@Value)[1]','VARCHAR(15)') AS SubmittedByValue,
    	attributes.value('local-name(.)','VARCHAR(25)') Attribute,
    	attributes.value('.','VARCHAR(15)') AS Attribute_Value
    FROM @StrXML.nodes('PDI_Approval_Message') am(c)
    	CROSS APPLY am.c.nodes('Header')Header(header)
    	CROSS APPLY am.c.nodes('Transactions/Transaction/Attributes/*')Attributes (attributes)

    2012年7月6日 16:08