Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Create table from Exec
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 06/01/2005 :  10:09:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 06/01/2005 :  16:32:07  Show Profile  Reply with Quote
Why do you like calling the query with exec?
You can call it like
Select * into mytable from (select * from yourtable) tableAlias


Niels
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 06/01/2005 :  16:36:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Edited by - tkizer on 06/01/2005 16:37:30
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 06/02/2005 :  01:07:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

raclede
Posting Yak Master

Philippines
180 Posts

Posted - 06/02/2005 :  01:22:51  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22864 Posts

Posted - 06/02/2005 :  01:30:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Thanks raclede. I will try that

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 06/02/2005 :  12:14:50  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

jmoore541
Starting Member

USA
1 Posts

Posted - 12/10/2010 :  08:23:23  Show Profile  Reply with Quote
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
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 12/10/2010 :  12:54:59  Show Profile  Reply with Quote
For a 1st time post.....tagged onto a 5 year old original message, you've done quite well to say nothing.
Go to Top of Page

TamaraS
Starting Member

1 Posts

Posted - 01/13/2011 :  15:28:54  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000