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

2010年8月23日 11:30

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

版主

0
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
- 已编辑 Kalman TothMicrosoft Community Contributor, Moderator 2012年7月6日 22:09
- 回复
- 引用
2010年8月23日 12:57

0

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日 13:21

0

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日 14:05

版主

0

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日 17:01

0

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

i did below to use OPENQUERY ,

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日 18:11

0
If you turn the stored procedure into a table-valued function you could then do this:
```
SELECT * INTO #tempTable
FROM
fn_test_sp(@strXML)
```
- 已标记为答案 Yazdani ISTS 2010年8月23日 22:16
- 回复
- 引用
2010年8月23日 19:10

0

> 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日 20:20

版主

0
>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
- 已标记为答案 Yazdani ISTS 2010年8月23日 22:15
- 回复
- 引用
2010年8月23日 22:15

0

thank you all
- 回复
- 引用
2010年8月23日 22:18

0

but I think in function we need to give table defintion..what say?
- 回复
- 引用
2010年8月23日 22:21

0

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
- 回复
- 引用
2011年11月29日 19:39

0

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.
- 回复
- 引用
2012年7月6日 14:26

0

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日 16:08

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)

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

全部回复

产品

资源

解决方案

更新

试用版

相关站点

培训

认证

其他资源

产品支持

其他支持