Dynamically create table when executing a stored procedure

  • Hi all,

    How can i create a temporary table on executing a procedure.

    ex:

    exec sp_who

    how to dinamically create temporary tabe on executing the above procedure

    from

    killer

  • Do you mean you want to store the resultset from the procedure in a temporary table? If so, it can't be dynamically created. You can however create it 'manually' and store the results there.

    CREATE TABLE #who ( define all columns that exist in the output of sp_who )

    INSERT INTO #who EXEC sp_who

  • Thanx Chris,

    This is not what i need i dont want to define the columns that exist in the output

    Is there anycommand that we can create a table without defining the columns.

    from

    Killer

  • I don't say it can't be done... but it would take so much work that I think it would be simpler to create a table with dummy col names (col001, col002...). I assume here that this is for general knowledge, do you have an actual situation in mind?

  • The code snippet below is what I use to sort the results of sp_who2. This is  what Chris suggested.  It is specific to sp_who2.

    If you need to work with the results of a stored procedure known at design time, this should work.  The only maintenance required is changing the table definition if the output of the stored procedure changes.

    Are you wanting a more generic solution that will store the results of some procedure that is not known at design time (maybe passed as a parameter)?

     

    ------------------------------

    DECLARE @Field as varchar(15)

    set @Field = 'DBName'

    DROP TABLE #SPWHO

    CREATE TABLE #SPWHO

    (SPID int,

    Status varchar(100),

    Login varchar(100),

    HostName varchar(100),

    BlkBy varchar(100),

    DBName varchar(100),

    Command varchar(100),

    CPUTime int,

    DiskIO int,

    LastBatch varchar(50),

    ProgramName varchar(100),

    SPID2 int)

    INSERT INTO #SPWHO

         EXEC master.dbo.sp_who2

    SELECT * FROM #SPWHO

    ORDER BY DBName

    ------------------------------

  • UPDATE:  My previous post included some stray code from an experiment.  There is no need for the lines

                           DECLARE @Field as varchar(15)

                           set @Field = 'DBName' 

     

  • This seems like the most evil idea... but it would work :

    Select * into NewTable from Openrowset (linkedserverinfo, 'Exec procedure...')

  • Hi ,

    Thanx  Johnson for ur effort but i mentioned before dont define the table structure before.

    Hi Remi,'

    But openrowset doesnt work.

    from

    killer

     

     

  • I'll try to make it work tomorrow.. but I know it's possible.

  • Remi,

    One attribute of Openrowset requires the connection to return columns.  This may be the problem when executing sp_who or sp_who2 depending on the provider used.

    Raj,

    The code was an example if you could predefine the table.  The question was are you looking for a solution specific to sp_who or something more generic that will handle procedures identified at run time?

     

  • Here's something that I just tested:

    I created a linked server on my machine :

    exec sp_addlinkedserver @Server = 'TestLinkServer',

    @srvproduct = 'SQLServer OLEDB Provider',

    @provider = 'SQLOLEDB',

    @datasrc = 'CASSANDRA'

    Then did the following:

    Select *

    into #NewTable

    from Openquery(TestLinkServer, 'Exec sp_who')

    select * from #NewTable

    drop table #NewTable

    worked like a charm....







    **ASCII stupid question, get a stupid ANSI !!!**

  • Yup, I was trying to make it work without the self-linked server but looks like there's no way around... unless we ask Noeld

  • sure we can ask noeld (not that there's anything wrong with that) - but wasn't he on the other post that had the exact same question...????

    will go and check!







    **ASCII stupid question, get a stupid ANSI !!!**

  • Don't think so. That post is somewhat incomplete at the moment.

  • Remi,

    Yes you can use Openrowset:

    Select *

    into #T1

    from Openrowset('SQLOLEDB','Trusted_connection=yes;Data Source=YOURSERVER','exec sp_who') dt

    select * from #T1

    drop table #T1

    -- You need to have MSDTC ON and the server set to allow remote server connections but other than that it should be OK

     


    * Noel

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply