Temp table is not getting filled up

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)

Temp table is not getting filled up

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-07-24 : 16:44:00

Hi I have a view as the following: This view has SalesPerson as a alias name.
(PROJ.Last_Name + ' ' + PROJ.First_Name AS SalesPerson)

Now I need to test a prototype procedure and then extend the concept to the real one.

I am trying to create a temptable that will have the projectno and salesperson information from the estimate (after a groupby) However when I am running this it is not inserting any record in the temptable. When I do a simple select statement
as follows I get close to 1100 records. I am not sure why the temp table is not getting filled up with the salesperson data. Any help is appreicated. Thanks

select projectno, salesperson
from estimate3
group by projectno, salesperson

CODE FOR VIEW

CREATE VIEW dbo.Estimate3
AS
SELECT EST.ProjectNo + '-' + EST.EstimateNo AS FullEstimateNo, EST.*, dbo.Plant.Plant_Desc AS PlantName, PROJ.Project_Name AS Project_Name,
ENG.Name AS EngineerName, dbo.tblMaterial.FamilyCode AS FamilyCode, dbo.tblEstimateMaterial.QtyPer AS QtyPer,
dbo.tblEstimateMaterialCost.QuoteUnitCost AS QuoteUnitCost, PROJ.YearlyQty, PROJ.aQty, PROJ.annVol1, PROJ.accManager,
PROJ.Last_Name + ' ' + PROJ.First_Name AS SalesPerson
FROM dbo.Plant RIGHT OUTER JOIN
dbo.estimate_tr EST INNER JOIN
dbo.tblMaterial INNER JOIN
dbo.tblEstimateMaterial ON dbo.tblMaterial.MaterialKey = dbo.tblEstimateMaterial.MaterialKey ON
EST.EstimateKey = dbo.tblEstimateMaterial.EstimateKey INNER JOIN
dbo.tblEstimateMaterialCost ON dbo.tblEstimateMaterial.EstimateMaterialKey = dbo.tblEstimateMaterialCost.EstimateMaterialKey LEFT OUTER JOIN
dbo.Project2 PROJ ON PROJ.ProjectNo = EST.ProjectNo ON dbo.Plant.Plant = EST.Plant LEFT OUTER JOIN
dbo.Engineer ENG ON EST.Engineer = ENG.User_ID

CODE FOR STORED PROCEDURE

alter proc sp_checking_salesperson
@family_code varchar(50)
As
create table #temptest(
projectno varchar(50),
salesperson varchar(100)
)

select projectno, salesperson
into #temptesting
from estimate3
group by projectno, salesperson

select * from #temptest

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-24 : 16:56:56

If you use SELECT/INTO, then do not first create the temp table. SELECT/INTO does that for you.

If instead, you use INSERT INTO/SELECT, then you need to create the temp table first.

Also notice how your temp tables are differently named: #temptest, #temptesting. So #temptesting has the data in it and #temptest (which you shouldn't be creating as noted above) is empty.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

gamaz2
Starting Member

31 Posts

Posted - 2008-07-24 : 16:58:25

Thanks for pointing out the mistake. I got it clear now Tara. Regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources