Last Updated
Viewed 1,891,018 Times

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]?

Select all data from BusinessLine into tmpBusLine works fine.

select *
into tmpBusLine
from BusinessLine

I am trying the same, but using a stored procedure that returns data, is not quite the same.

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

Output message:

Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'exec'.

I have read several examples of creating a temporary table with the same structure as the output stored procedure, which works fine, but it would be nice to not supply any columns.

I've looked at other questions on Stack Overflow related to this question, but none of them seemed to answer this question clearly.

We have a system Stored Procedure called sp_who2 which returns a result set of information for all running processes on the server. I want to filter the data returned by the stored procedure; conceptually, I might do it like so:

SELECT * FROM sp_who2
WHERE login='bmccormack'

That method, though, doesn't work. What are good practices for achieving the goal of querying the returned data of a stored procedure, preferably without having to look of the code of the original stored procedure and modify it.

I need to extract records from a table, copy the data to a second table and then update the records in the first table to indicate that they have been copied across successfully.

My current SP code is this:

SELECT TBL_ADDRESSBOOKADDRESSES.* FROM TBL_ADDRESSBOOKADDRESSES 
INNER JOIN TBL_CAMPAIGNS
ON TBL_ADDRESSBOOKADDRESSES.adds_ABMId = TBL_CAMPAIGNS.campaign_AddressBook
WHERE TBL_CAMPAIGNS.campaign_Status = 1

Now once the above is performed i need to insert this data into a second table called TBL_RECIPIENTS. Assume that the columns are simply named col_1, col_2, col_3 .... col_5 in TBL_ADDRESSBOOKADDRESSES and that this is the same in TBL_RECIPIENTS.

Once this action is performed i need to update TBL_CAMPAIGNS.campaign_Status = 2 Ideally this should only be for those records that have actually been updated(in case script gets stopped mid way through due to server crash etc)

Please let me know if you need anything clarifying.

Many Thanks!


Ive taken the advise kindly given below and come up with the working code below. Ive read tutorial which suggested adding try/catch to ensure rollback if any errors occurr. Is my code below adequate in this respect??

Any suggest would be gratefully received.

Thanks.

CREATE PROCEDURE web.SERVER_create_email_recipients
AS
BEGIN TRY
  --sets (n) campaigns ready for transfer of emails to mailing list
      UPDATE TOP(1) TBL_CAMPAIGNS
  SET TBL_CAMPAIGNS.campaign_Status = 1
  WHERE TBL_CAMPAIGNS.campaign_Status = 0

  --finds above marked campaigns, retreives addresses then copies them to     TBL_CAMPAIGNRECIPIENTS ready for auto mailout
  INSERT TBL_CAMPAIGNRECIPIENTS (recip_CampaignId, recip_Email, recip_Forename, recip_Surname, recip_adds_Key)
  SELECT C.Campaign_AddressBook, ABA.adds_Email, ABA.adds_RecipientForename,     ABA.adds_RecipientSurname, ABA.adds_Key
  FROM TBL_ADDRESSBOOKADDRESSES ABA
  JOIN TBL_CAMPAIGNS C ON ABA.adds_ABMId = C.campaign_AddressBook
  WHERE C.campaign_Status = 1

  --checks that above emails have been copied across and then sets the campaigns status accordingly
  UPDATE C
  SET C.campaign_Status = 2
  From TBL_CAMPAIGNS C
  JOIN TBL_ADDRESSBOOKADDRESSES  aba
  ON aba.adds_ABMId = C.campaign_AddressBook
  JOIN TBL_CAMPAIGNRECIPIENTS r on aba.adds_Key = r.recip_adds_Key
  WHERE C.campaign_Status = 1

END TRY
BEGIN CATCH
  -- Whoops, there was an error
  IF @@TRANCOUNT > 0
     ROLLBACK

  -- Raise an error with the details of the exception
  DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
  SELECT @ErrMsg = ERROR_MESSAGE(),
         @ErrSeverity = ERROR_SEVERITY()

  --throws out error to logs?
  RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

I've used a table valued parameter before, but I'm not sure how to use xml.

I do not know the best way to format my xml but I think I would try this:

<Car>  
   <Name>BMW</Name>  
   <Color>Red</Color>  
</Car>

Then I would pass the xml (one or more car) to the stored procedure and it would insert one row for each car I pass (with the name going in a name column etc..)

Does anyone know how to write the stored procedure? (I'd usually try it myself but I don't have much time for testing T_T)

Similar Question 4 (4 solutions) : Executing a stored procedure within a stored procedure

Similar Question 5 (3 solutions) : Sql query for stored procedure

Similar Question 6 (8 solutions) : IF/ELSE Stored Procedure

Similar Question 7 (2 solutions) : Converting query to a stored procedure

cc