How to load data into table from store procedure?

• kokyan

  SSCommitted

  Points: 1584

  More actions

  February 10, 2004 at 3:47 am

  #107262

  Dear Experts,

  I want to load data from a store procedure to a temporary table. As long as I know, we cannot using SELECT...INTO (dynamically), like:

  SELECT INTO #TBL EXEC USP_STOREPROC

  We need to create the table first and specify the columns, as well as datatype. Only after the table created, we can load the data from store procedure to the table:

  INSERT INTO #TBL EXEC USP_STOREPROC

  The problem is I do not know the structure of the temporary table before run the store procedure. Any idea how to dynamically create the temporary table?

   

  
  
  Regards,
  kokyan
  

• Antares686

  SSC Guru

  Points: 125444

  More actions

  February 10, 2004 at 6:54 am

  #493366

  You could use a linked server (even to the server on) and using openquery to call the sp do the select into:

   

  Ex:

   

  Select * into #x from openquery (linksrv,'sp_name')

   

  but you are limited in that you cannot pass in conditions to the sp unless you write using dynamic SQL.