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.
| Author |
Topic |
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
 Posted - 2014-05-12 : 22:23:27
|
| I have a long query.... I am looking to make it a table when the query is ran. If it exists, then it needs to be dropped and then re-created. What would be the correct syntax? Is the front part right and what would be the structure of selecting the results of a query to populate MyTable? I would like a structure where I can just wrap my long query inside of it.IF EXISTS ([MyTable])BEGIN DROP TABLE [MyTable]END |
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-12 : 23:13:37
|
| I tried this format without the drop and got an errorSELECT * INTO MyTable FROM (MyQuery);MyQuery ends with an ORDER By statement and I get this error:The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.Just trying to get an example with the right syntax to Create a table if it does not exist or drop if it does and then recreate with a query result. |
 |
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-12 : 23:40:03
|
| Based on what I read, the syntax should be similar to this, but I am getting error messages... anyone out there can quickly decipher it?MyQuery is referencing results from Query A which became CteTableA===================================================;with cteTableA as (QueryA)IF OBJECT_ID('dbo.MyTable', 'U') IS NOT NULL DROP TABLE [dbo].[MyTable]SELECT * INTO MyTable FROM (MyQuery); |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-13 : 01:26:41
|
| What error message are you getting? for the second attemptJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-05-13 : 01:38:08
|
| I figured it out...The Drop if exists needs to be up topthen the with cteTableA lineThe Into should be within the MyQuery Not very intuitive, but it gets the job done and the table created with query results |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-19 : 01:40:45
|
| Another factor to consider - if you mix DML and DDL in the same procedure - it will cause a recompilation - which may have a negative impact on performance.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
Create a New Table Out of Query Results