inserting results into temp table

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

General SQL Server Forums

New to SQL Server Programming

inserting results into temp table

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2012-02-13 : 11:58:50

Hi, was just wondering eny the below doesnt quite work?
Something to do with the insertion?

select * into #temptable
from

(
select b.JobID,a.* from F_Audit a inner join D_JobAttributes b
on a.JobKey = b.JobKey
where a.JobKey in (
select jobkey from D_JobAttributes where JobID in
(
'080023200108',
'080023200110',
'080023200111',
'080023200112',
'080023200113',
'080280580202',
'080280580203',
'080280720101',
'080280720102',
'080280720103',
'080281510109',
'090290130103',
'090291280103',
'090291280104',
'0903141801',
'0903284002',
'090340000105',
'090344360520',
'090354290107',
'090355700166',
'090355700265',
'100000010105',
'100035380102',
'100035380104',
'100105960103',
'100173500101',
'100176090102',
'100188440104',
'100193220102',
'100329640103',
'100343160102',
'100343160103',
'100418120103',
'100448190102',
'100448190103',
'100453810103',
'100502830102',
'100502830103',
'100502830104',
'100519490102',
'100519490103'
))
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 12:07:22

quote:
Originally posted by tariq2

Hi, was just wondering eny the below doesnt quite work?
Something to do with the insertion?

select * into #temptable
from

(
select b.JobID,a.* from F_Audit a inner join D_JobAttributes b
on a.JobKey = b.JobKey
where a.JobKey in (
select jobkey from D_JobAttributes where JobID in
(
'080023200108',
'080023200110',
'080023200111',
'080023200112',
'080023200113',
'080280580202',
'080280580203',
'080280720101',
'080280720102',
'080280720103',
'080281510109',
'090290130103',
'090291280103',
'090291280104',
'0903141801',
'0903284002',
'090340000105',
'090344360520',
'090354290107',
'090355700166',
'090355700265',
'100000010105',
'100035380102',
'100035380104',
'100105960103',
'100173500101',
'100176090102',
'100188440104',
'100193220102',
'100329640103',
'100343160102',
'100343160103',
'100418120103',
'100448190102',
'100448190103',
'100453810103',
'100502830102',
'100502830103',
'100502830104',
'100519490102',
'100519490103'
))
)t

you're missing an alias

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 12:07:36

You're missing properly written code



	 SELECT b.JobID,a.* 
	   INTO #temptable
	   FROM F_Audit a 
     INNER JOIN D_JobAttributes b
		 ON a.JobKey = b.JobKey
          WHERE jobID IN (
  '080023200108'
, '080023200110'
...
)

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 12:09:59

If its one time query its fine

otherwise it would probably be better to put the ids along with job keys onto a temporary table and then use exists to check if jobid is one among the ids in temporary table. you can also add an index on temporary table to speed up search based on jobid especially if number of ids involved is large

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 12:12:53

..or


	 SELECT * 
	   INTO #temptable
	   FROM F_Audit a 
     INNER JOIN D_JobAttributes b
	     ON a.JobKey = b.JobKey
     INNER JOIN (SELECT '080023200108' AS JobID UNION ALL
	         SELECT '080023200110' AS JobID UNION ALL
					....
			) AS c
             ON b.JobID = c.JobID

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-13 : 12:14:31

quote:
Originally posted by visakh16

If its one time query

Biggest lie in the b'dness

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tariq2
Posting Yak Master

125 Posts

Posted - 2012-02-13 : 12:20:05

thank you guys, very helpful :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-13 : 12:25:36

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources