Linking 2 temp tables for an Insert

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

Linking 2 temp tables for an Insert

Author

Topic

killtacularmania
Starting Member

22 Posts

Posted - 2010-03-25 : 11:54:11

Hey everyone, basically I am creating two temp tables and then I want to link the two temp tables at the end to do an insert into statement. The last insert into statement is what draws and error. If I run the first two temp tables I get no errors but for the insert I get the error "Must declare the Scalar Variable "@PcpMostMYear"....and yes my first temp table and the true sql table are named the same not sure if thats the problem...here is my script

Truncate Table PcpMostMYear

--Create table to hold MemNumber,ProvNum,and the Number of Visits for that provider per member

DECLARE @PcpMostMYear TABLE (MsfcNumber varchar(30),ProvNum varchar(30),NumOfVisits int)
INSERT INTO @PcpMostMYear (MsfcNumber,ProvNum,NumOfVisits)
SELECT VISIT.MEM_NBR,VISIT.PROV_NBR, COUNT(*) AS NumOfVisits
FROM VISIT INNER JOIN
PROV_PRAC_TYPE ON VISIT.PROV_NBR = PROV_PRAC_TYPE.PROV_NBR INNER JOIN
Chases ON VISIT.MEM_NBR = Chases.MemberNumber
WHERE (PROV_PRAC_TYPE.PRAC_TYPE_ID = 1) AND (VISIT.SERVICE_DT BETWEEN CONVERT(DATETIME, '2009-01-01 00:00:00', 102) AND
CONVERT(DATETIME, '2009-12-31 00:00:00', 102))
GROUP BY VISIT.MEM_NBR, VISIT.PROV_NBR,PROV_PRAC_TYPE.PROV_NBR
HAVING (NOT (PROV_PRAC_TYPE.PROV_NBR LIKE '555%'))
ORDER BY VISIT.MEM_NBR, NumOfVisits Desc

--Create Temp Table to hold just the MemNumber and their number of visits

DECLARE @NumOfVisits Table (MsfcNumber varchar(30),NumOfVisits int)
Insert Into @NumOfVisits(MsfcNumber,NumOfVisits)
SELECT MsfcNumber, MAX(NumOfVisits) AS NumOfVisits
FROM
PcpMostMYear
GROUP BY MsfcNumber
ORDER BY MsfcNumber

--Link the two temp tables to get the prov with the most office visits per member and load into a sql table.

Insert Into PcpMostMYear(MsfcNumber,ProvNum,NumOfVisits)
SELECT @PcpMostMYear.MsfcNumber, @PcpMostMYear.ProvNum, @PcpMostMYear.NumOfVisits
FROM @PcpMostMYear INNER
JOIN
@NumOfVisits ON @PcpMostMYear.MsfcNumber
= @NumOfVisits.MsfcNumber AND
@PcpMostMYear.NumOfVisits
= @NumOfVisits.NumOfVisits
ORDER BY @PcpMostMYear.MsfcNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 11:56:55

can you explain with some sample data the output you want. show some rows from each of above temp tables and explain what should be o/p out of them.

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

killtacularmania
Starting Member

22 Posts

Posted - 2010-03-25 : 12:15:15

No problem,

Temp Table one for example shows this:

MsfcNumber ProvNum NumOfVisits
123456 1456 13
123456 1564 31
123456 1234 1
321654 1456 10
321654 9876 5

Temp Table two for example show this: It takes the max num of visits for each member.

MsfcNumber NumOfVisits
123456 31
321654 10

I then want to link the tables using the MsfcNumber and NumOfVisits to pull the ProvNum

MsfcNumber ProvNum NumOfVisits
123456 1564 31
321654 1456 10

Because a member could have multiple provnum's a simple group by using max on num of visits doesn't work. Hopefully this helps

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 12:19:51

[code]SELECT p.MsfcNumber,p.ProvNum,p.NumOfVisits
FROM @PcpMostMYear p
JOIN @NumOfVisits v
ON v.MsfcNumber = p.MsfcNumber
AND v.NumOfVisits = p.NumOfVisits
[/code]

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

killtacularmania
Starting Member

22 Posts

Posted - 2010-03-25 : 13:23:18

this might be a dumb question but what does the p and v represent? thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-25 : 13:26:49

there are aliases ie short names for tables

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources