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

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

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

Author

Topic

ajmaly
Starting Member

11 Posts

Posted - 2010-08-23 : 13:31:41

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

To RUN,

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 TEST_SP @StrXML

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...

ajmal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:44:06

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources