May 25, 2007 at 5:39 am
I got an Error Like
There is already an object named '#TempTable' in the database.
My Script is here. First Check it
--------------------------------------------
Create Table dbo.#TempTable
(
property_id int,
sellprice float,
bedrooms varchar(50),
sub_loc_name varchar(500),
cat_id int,
landlord_id int
)
Select distinct property_id,sellprice,bedrooms,sub_loc_name,cat_id,landlord_id
Into dbo.#TempTable
From [mktest].[dbo].[Table]
Select * from dbo.#TempTable
Drop Table dbo.#TempTable
------------------------------------------------
Whats wrong in my script? If i give a unique Name it also rejects and said that It already exists And when I try to delete it, Message says that This TempTable does not Exixts.
I hope you will understand.
May 25, 2007 at 5:59 am
Hi
The error is: First you are creating #TempTable with Create Table statement, then again you are calling one select statement to create #TempTable.
If u want to insert data in #TempTable table, use below query:
Create Table dbo.#TempTable
(
property_id int,
sellprice float,
bedrooms varchar(50),
sub_loc_name varchar(500),
cat_id int,
landlord_id int
)
Insert into #TempTable(property_id,sellprice,bedrooms, sub_loc_name,cat_id,landlord_id) Select distinct property_id,sellprice,bedrooms,sub_loc_name,cat_id,landlord_id
From [mktest].[dbo].[Table]
Select * from dbo.#TempTable
Drop Table dbo.#TempTable
Amit
May 25, 2007 at 6:00 am
ELSE
u can use only select statement:
Select distinct property_id,sellprice,bedrooms,sub_loc_name,cat_id,landlord_id
INTO #TempTable From [mktest].[dbo].[Table]
Select * from dbo.#TempTable
Drop Table dbo.#TempTable
Amit
May 25, 2007 at 7:44 am
Dear Amit Kumar
Your 1st Script is working fine yet.
Very Thanks for your help.
Regards
Syed Yasir Aziz
Karachi
May 28, 2007 at 1:51 am
Continue with the previous Post, What I want to do is
To get Values from #TempTable one by one. Consider the below case
Prop_ID Cat_id
----------------
1 1
1 1
2 1
2 1
2 1
Now I want to get Distinct Prop_ID but One by One. Here, Distinct Prop_Id are 1,2
I need "1" Use it in another query then return to #temptable and then get "2" then use it to another query and so on.
If I use While Loop, What it returns a One by One Recordset not a complete Recordset. I need Complete.
Please Help
May 29, 2007 at 6:51 am
Hello,
the problem with your question is, that you ask how to achieve certain things, but not why do you want it. Generally, requirement to get some values one by one should be avoided if possible. SQL works best with data sets. There is a possibility that you could write the query in a different way, with much better results.
Could you explain what do you use it for, and what "use it in another query" means? Also, I don't understand why do you complain that while loop returns one by one... that's what you wanted...?!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply