SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Add to Temporary Table.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

davidshq
Posting Yak Master

USA
119 Posts

Posted - 10/15/2005 :  10:22:28  Show Profile  Visit davidshq's Homepage  Reply with Quote
I have a temporary table that is created as part of an If..Then statement:
If @var=1
BEGIN
select * into #temptable from table1 WHERE data=1
END
If @var=2
BEGIN
select * into #temptable from table1 WHERE data=2
END
But, this creates problems b/c the #temptable is already created by the time it gets to the second variable and SQL doesn't just add the information, instead it throws an error. Is there another statement I dont know that adds info. to the temptable rather than trying to create a new one?
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/15/2005 :  11:07:22  Show Profile  Reply with Quote
>>Is there another statement I dont know that adds info
To add data to an existing table use INSERT INTO <table>. (see books online)

ie:
insert #temptable (<colList>)
select <colList> from table1 where...



Be One with the Optimizer
TG
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/15/2005 :  11:19:14  Show Profile  Reply with Quote
TG gave You the answer.
You will have to create the #temp table first though.
CREATE TABLE, is the statement to use, see BOL.


I just wanted to say thanks for reminding you of thehungersite in your signature
I had just about forgotten about those, used to support them commonly before (thehungersite and thrainforestsite).
Will pick it up again.

I looked at the stats for the different sites,
and was a bit shocked about which ones were most popular.
Damnit, people should take care of their pets if they get any!

Here we go:
Year 	 	Clicks
#1
BREAT CANCER SITE (Free Mammograms)
2004 	 	59,265,651
2003 	 	49,211,445
2002 	 	37,363,847
#2
ANIMAL RESCUE SITE (Feed abandoned pets)
2004 	 	52,587,337
2003 	 	39,948,086
2002 	 	11,328,962
#3
HUNGER SITE SITE
2004 	 	39,809,114
2003 	 	38,785,490
2002 	 	40,798,844
#4
RAINFOREST SITE
2004 	 	26,522,578
2003 	 	23,916,601
2002 	 	21,526,818
#5
CHILD HEALTH SITE
2004 	 	25,390,774
2003 	 	22,869,573
2002 	 	5,038,236



rockmoose
Go to Top of Page

davidshq
Posting Yak Master

USA
119 Posts

Posted - 10/15/2005 :  12:15:31  Show Profile  Visit davidshq's Homepage  Reply with Quote
Yeah, those stats are very interesting. Thanks all for your help. But I'm still getting an error. Here is my actual code:
ALTER PROCEDURE dbo.AllGames
(
@varAI int,
@varSI int,
@varII int,
@varCG int,
@varAO int,
@varSO int,
@varIO int,
@varWG int,
@varMG int,
@varTrick int,
@varUserName varchar(200),
@varSQL varchar(1000),
@varSearch int
)
AS
select uID, gID, LastPlayedDate into #templastplayed from LastPlayed where uID LIKE @varUserName
select uID as uID2, gID as gID2, Popularity as Popularity2 into #temppopularity from GamePopularity where uID LIKE @varUserName
If @varSearch = 0
BEGIN
select * from Game G LEFT JOIN #templastplayed LP ON G.ID=LP.gID LEFT JOIN #temppopularity GP ON G.ID=GP.gID2 ORDER BY G.Type
END
If @varSearch = 1
BEGIN
select * into #tempfilter from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=0
If @varAI=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varSI=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varII=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varCG=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varAO=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varSO=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varIO=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varWG=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varMG=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
If @varTrick=1
BEGIN
Insert Into #tempfilter * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1
END
END
/* SET NOCOUNT ON */
RETURN

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/
Go to Top of Page

davidshq
Posting Yak Master

USA
119 Posts

Posted - 10/15/2005 :  13:23:49  Show Profile  Visit davidshq's Homepage  Reply with Quote
I figured it out. I needed a select after the *.
David.

- http://www.civilwarsearch.com/
- http://www.thehungersite.com/
- http://www.grid.org/
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/15/2005 :  13:27:39  Show Profile  Reply with Quote
Insert Into #tempfilter SELECT * from Game G LEFT JOIN #templastplayed LP on G.ID=LP.gID LEFT JOIN #temppopularity GP on G.ID=GP.gID2 WHERE G.Type=1

That will probably solve the error you are getting...

But you should rewrite the whole proc, the table #tempfilter is not used for anything useful in the proc!?
And you insert exactly the same data several times, for various conditions, why?

It is possible to write conditions like this:
IF @varAI=1 OR @varSI=1 OR @varII=1
->do something if condition is true

rockmoose
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 10/15/2005 :  13:28:36  Show Profile  Reply with Quote

But the proc still looks weird !
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000