Create table from Exec - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Create table from Exec

Author

Topic

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-01 : 10:09:25

It is possible to move the result set of Exec to the table if the table is already created
Insert into mytable(col1,...coln) Exec(query). This works well

But is it possible to have
Select * into mytable from Exec(query)
Any ideas?

Madhivanan

Failing to plan is Planning to fail

nieurig
Starting Member

8 Posts

Posted - 2005-06-01 : 16:32:07

Why do you like calling the query with exec?
You can call it like
Select * into mytable from (select * from yourtable) tableAlias

Niels

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-01 : 16:36:51

quote:
Originally posted by madhivanan

It is possible to move the result set of Exec to the table if the table is already created
Insert into mytable(col1,...coln) Exec(query). This works well

But is it possible to have
Select * into mytable from Exec(query)
Any ideas?

That is not possible. You must use INSERT INTO EXEC. SELECT * INTO shouldn't be used anyway except if you need the IDENTITY function.

Tara

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 01:07:43

The reason why I want to do is I have stored procedure in which I pass query as parameter. I want to compare the result set with other table. But it will be possible only if I move the resultset to temp table.

I got other solution. I used Select * into temptable inside Exec and it works well

quote:
SELECT * INTO shouldn't be used anyway except if you need the IDENTITY function.

Tara, Can you give an example for this

Madhivanan

Failing to plan is Planning to fail

raclede
Posting Yak Master

180 Posts

Posted - 2005-06-02 : 01:22:51

you can use table-valued UDF

Select * into mytable from dbo.Sample(parameters)

"If the automobile had followed the same development cycle as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year, killing everyone inside. "

raclede

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-02 : 01:30:11

Thanks raclede. I will try that

Madhivanan

Failing to plan is Planning to fail

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 12:14:50

quote:
Originally posted by madhivanan

The reason why I want to do is I have stored procedure in which I pass query as parameter.

You pass a query in as a parameter?!!! You should never ever pass in queries to a parameter. What happens if someone hacks into your system and passes DROP TABLE YourMostImportantTable?

I guess you aren't concerned about security.

Using a UDF for this is a hack. Do it the right way.

Tara

jmoore541
Starting Member

1 Post

Posted - 2010-12-10 : 08:23:23

quote:
Originally posted by tkizer

quote:
Originally posted by madhivanan

It is possible to move the result set of Exec to the table if the table is already created
Insert into mytable(col1,...coln) Exec(query). This works well

But is it possible to have
Select * into mytable from Exec(query)
Any ideas?

That is not possible. You must use INSERT INTO EXEC. SELECT * INTO shouldn't be used anyway except if you need the IDENTITY function.

Tara

Joe Moore

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-10 : 12:54:59

For a 1st time post.....tagged onto a 5 year old original message, you've done quite well to say nothing.

TamaraS
Starting Member

1 Post

Posted - 2011-01-13 : 15:28:54

quote:
Originally posted by tkizer

That is not possible. You must use INSERT INTO EXEC. SELECT * INTO shouldn't be used anyway except if you need the IDENTITY function.

Tara

Hi,
I know this is an old post, but hopefully new replies are still read.

Why shouldn't you use SELECT * INTO? The entire statement or are you saying it is better to specify the column name instead of *?

Thanks.
Tamara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources