create table and exec

Question

Post reply

create table and exec

Samx

Ten Centuries

Points: 1021
More actions
April 10, 2007 at 9:40 am

#116842

Hi,
Is it possible to create a table from an exec(select ....)
Regards

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736183

April 10, 2007 at 10:02 am

#699588

Yes, exec( 'create table( myid int)')

Samx Ten Centuries Points: 1021 More actions · Answer 2

No I would like to create a table from the following exec request :

exec (select * from sysdatabases)

But I don't want to change the exec part !!!

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 3

exec ('select * into TempTable from sysdatabases')

exec ('select * into ##TempTable from sysdatabases')

If you use single #, the table is not accesible outside the exec statement

N 56°04'39.16"
E 12°55'05.25"

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 4

One other option is to create your table and insert into it the results of the exec(sql) statement.

A simple example:

create table t1 (firstname varchar(50), lastname varchar(50), phone varchar(15))

insert into t1 exec('select firstname, lastname, phone from customers')

This is also handy if you want to capture the results of a stored procedure call. Say you wish to capture the results of a call to sp_who2 - create a table with the same structure as the resultset sp_who2 returns, then run

insert into who2 exec sp_who2

Carter Burleigh

But boss, why must the urgent always take precedence over the important?

Jonathan Wallace SSC Enthusiast Points: 153 More actions · Answer 5

I would like to do what cburleigh suggested with a little extra.

I'm looking to aggregate information from exec statements into the temporary table along with identifying information as to the original source of the row.

For example, imagine two stored procedures which return a set of rows that in this case consist of a single string. I would like to be able to annotate which rows came from which stored procedure in the temporary table as well as give them unique ids. (pseudo-tsql):

create table #t1(unique_id int, src_proc varchar(50), name (varchar(50))

insert into #t1 exec sproc1(param1, param2)

insert into #t1 exec sproc2(param3, param4)

After the two inserts, #t1 would look like:

1, "sproc1", "row 1 from sproc1"

2, "sproc1", "row 2 from sproc1"

...

n-1, "sproc2", "row x-1 from sproc2"

n, "sproc2", "row x from sproc2"

where n is the amount of rows in the temporary table and equal to x + y where x and y are the number of rows generated by sproc1 and sproc2, respectively.

Thanks in advance for your assistance.

Jonathan Wallace SSC Enthusiast Points: 153 More actions · Answer 6

My first thought this morning is to create temporary tables for each stored procedure that have the exact same schema as the result set returned by the stored procedures.

Then, have one master temp table, #master, that has the structure for #t1 defined above and a default value for null for src_proc. After an insert into select * for each stored procedure, I can update #master set, src_proc = "sprocn" where src_proc IS NULL where "sprocn" refers to the appropriate stored procedure.

However, this feels very kludgy. Is there a simple solution here that I'm missing?