T-SQL #TempTable headace
-
I have a fairly complex piece of code that (in theory) needs to do something like this:
If [Condition1]
Select
Field1,Field2, Field3
into #Temp
from Table
order by Field3,Field2, Field1
Else
If [Condition2]
Select
Field1,Field2, Field3
into #Temp
from Table
Order By Field2, Field1, Field3
Else
Select
Field1,Field2, Field3
into #Temp
from Table
Order By Field1,Field2, Field3
Select # from #Temp as r For XML Auto
The problem is that the code won't compile. It returns an error stating that #Temp already exists. I am restricted from using dynamic SQL for this task and it is imperative that the data remain in the same sort order in which it was originally selected, so declaring a @Table variable hasn't worked.
Any help would be greatly appreciated.
Thanks,
-M
-
Something like this...
create table #Temp
(
Field1 some_date_type,
Field2 some_date_type,
Field3 some_date_type,
)
If [Condition1]
insert #temp( Field1,Field2, Field3)
Select Field1,Field2, Field3
from Table
order by Field3,Field2, Field1
Else
If [Condition2]
insert #temp( Field1,Field2, Field3)
Select Field1,Field2, Field3
from Table
order by Field3,Field2, Field1
Else
insert #temp( Field1,Field2, Field3)
Select Field1,Field2, Field3
from Table
order by Field3,Field2, Field1
Select # from #Temp as r For XML Auto
drop table #Temp
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
Just finish your script with
drop table #Temp
#Temp stays in tempdb until you close the connection.
_____________
Code for TallyGenerator -
--Try this in query analyzer:
Declare @variable as bit
Set @Variable = 1
If @Variable = 1
Begin
Select 'a' as [a], 'b' as , 'c' as [c] into #temp
End
Else
Begin
Select '1' as [a], '2' as , '3' as [c] into #temp
End
Select * from #temp
Drop table #Temp
--Then try this:
Declare @variable as bit
Set @Variable = 1
If @Variable = 1
Begin
Select 'a' as [a], 'b' as , 'c' as [c] into #temp
End
--Else
--Begin
--Select '1' as [a], '2' as , '3' as [c] into #temp
--End
Select * from #temp
Drop table #Temp
--Then maybe you will understand the problem.
--SQL will not compile the script if the select into #temp appears more than once
-
>>SQL will not compile the script if the select into #temp
And why should it ?
SQL is parsed. Then it is executed.
The parser has no way of knowing what will happen at execution time, therefore the parser has no way of knowing that the 2 select into statements are mutually exclusive operations. All it sees are 2 potential attempts to create the same object name, hence the parsing error.
As an aside, what are you expecting ORDER BY to do in the context of SELECT INTO ?
-
Mathew..have you tried creating the table before your conditional statements.and inserting into the created table(instead of doing insert into) ?
Mathew J Kulangara
sqladventures.blogspot.com -
The full scope of the task is to allow an application to pass in an xml string allowing the user to specify how many records to return, sorted by the user's field of choice in either ascending or descending order (user's choice) begining with the record following the last one at which the user was reviewing.
In this maner, everytime that a user hits the "next" button, the SQL query returns the next X records in order according to the users preference.
As I am sure you can imagine, if the user sorts the list by Zipcode then the top 25 records are going to be radically different than they would be if the same list were sorted by CompanyName.
P.S. I am resticted by company policy from using dynamic SQL for this.
So, if @SortField = CompanyName and @SortDirection = descending and @RecordCount = 125 and @LastData = '' (indicating that the user had no data to begin with) then select all of the data into @Table sorted by Companyname desc, set @Totalrecords = @@Rowcount then set Rowcount = @RecordCount and select * from @Table as r for XML Auto to return the right amount of data and compare @Total to @RecordCount so that if @Total <= @RecordCount return @MoreData = 0 else @MoreData = 1
BUT if @SortField = Zipcode and @SortDirection = Ascending and @RecordCount = 350 and @LastData = '80021' (indicating that the last recor din the user's current data set was zipcode 80021) then select all of the data into @Table sorted by ZipCode (ascending) where zipcode > 80021, set @Totalrecords = @@Rowcount then set Rowcount = @RecordCount and select * from @Table as r for XML Auto to return the right amount of data and compare @Total to @RecordCount so that if @Total <= @RecordCount return @MoreData = 0 else @MoreData = 1
-
Yes, this looks like the route I will need to take. I was having trouble getting the data in the correct order using method because the code below throws an error:
Insert into @Table (Field1, Field2, Field3)
(Select Field1, Field2, Field3 from Table Order By Field1, Field2, Field3)
But then I started experimenting and stumbled onto this:
Insert into @Table (Field1, Field2, Field3)
(Select Field1, Field2, Field3 from Table) Order By Field1, Field2, Field3
Which worked. go figger lol.
-
A temp table, like any other SQL table, is an unordered set of rows.
Putting rows into a temp table with an ORDER BY does not guarantee anything about the physical ordering of the rows in the target table, nor does it guarantee the records will be retrieved in any particular order in a subsequent SELECT.
The only safe way to gurantee a specific order on a set, is to use an ORDER BY in the SELECT.
Trying to create a "pre-ordered" table is a bug waiting to happen.
-
Order By in the Select Into gets the data into the temp tale in the specified order
-
Well lucky for you you know everything about everything. I will be dropping my membership to this list now as i cannot stomach your smug know-it-all attitude.
Before I go, try this (You will have to execute each segement seperately) and you will see that specifying the order by does in fact place the records into memory in the specified order
use pubs
select au_Lname, au_fname, phone
into #Temp
from dbo.Authors
order by Phone
Select * from #Temp
Drop Table #Temp
select au_Lname, au_fname, phone
into #Temp
from dbo.Authors
order by au_Fname
Select * from #Temp
Drop Table #Temp
select au_Lname, au_fname, phone
into #Temp
from dbo.Authors
order by au_lname
Select * from #Temp
Drop Table #Temp
-
>>I will be dropping my membership to this list now
Your loss.
Speaking of "lists", here's another SQL server "list".
Here's what they have to say about the matter. And look closely at the names, many are well respected book authors in the SQL community:
-
You could also try the following, which doesn't use a temp table:
Select Field1, Field2, Field3 from Table
order by
case when [Condition1] then Field3 else null end,
case when [Condition1] then Field2 else null end,
case when [Condition1] then Field1 else null end,
case when [Condition2] then Field2 else null end,
case when [Condition2] then Field1 else null end,
case when [Condition2] then Field3 else null end,
case when [Condition3] then Field1 else null end,
case when [Condition3] then Field2 else null end,
case when [Condition3] then Field3 else null end
Here Condition3 is equivalent to Condition1 and Condition2 being false.
The "order by" statement can be further simplified if Field1, Field2 and Field3 are of the same datatype.
There is no reason to stop using SSC just because PW forgot to put a
in his post... He is trying to help you and clearly knows what he is talking about. Please stay around and contribute. It is the combined knowledge of everyone involved that makes SSC a great place if you want to learn about SQL Server.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply