Add to Temporary Table. - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add to Temporary Table.

Author

Topic

davidshq
Posting Yak Master

119 Posts

Posted - 2005-10-15 : 10:22:28

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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-10-15 : 11:07:22

>>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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-15 : 11:19:14

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

davidshq
Posting Yak Master

119 Posts

Posted - 2005-10-15 : 12:15:31

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/

davidshq
Posting Yak Master

119 Posts

Posted - 2005-10-15 : 13:23:49

I figured it out. I needed a select after the *.
David.

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-15 : 13:27:39

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

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-10-15 : 13:28:36

But the proc still looks weird !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources