Using SELECT to INSERT - 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

General SQL Server Forums

New to SQL Server Programming

Using SELECT to INSERT

Author

Topic

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-08 : 12:58:18

Hey all. I'm taking a different approach on something.

I have my query which returns two pieces of data: facility id & number of reinstatements.

I've created a temporary table with those two pieces of data...
CREATE TABLE #Reactivations(facilityid varchar(10), reinstates int)

How can I use this query to insert the results into my table?

SELECT tblFacility.vcFacilityID as [Location], COUNT(distinct tblMembers.vcMemberID) AS Reactivations
FROM vwReactivatedContracts AS vwReactivatedContracts LEFT OUTER JOIN
tblFacility AS tblFacility ON vwReactivatedContracts.vcHomeFacilityId = tblFacility.vcFacilityID INNER JOIN
tblMembers AS tblMembers ON vwReactivatedContracts.vcOwnerId = tblMembers.vcMemberID
WHERE (vwReactivatedContracts.reactivation_date = @Day)
AND (vwReactivatedContracts.simembershipclassid in (Select * from #PrimaryClasses))
GROUP BY tblFacility.vcFacilityID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:01:00

[code]
INSERT INTO #Reactivations
SELECT tblFacility.vcFacilityID , COUNT(distinct tblMembers.vcMemberID)
FROM vwReactivatedContracts AS vwReactivatedContracts LEFT OUTER JOIN
tblFacility AS tblFacility ON vwReactivatedContracts.vcHomeFacilityId = tblFacility.vcFacilityID INNER JOIN
tblMembers AS tblMembers ON vwReactivatedContracts.vcOwnerId = tblMembers.vcMemberID
WHERE (vwReactivatedContracts.reactivation_date = @Day)
AND (vwReactivatedContracts.simembershipclassid in (Select * from #PrimaryClasses))
GROUP BY tblFacility.vcFacilityID
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:02:24

and if you want to create table on the fly from query rather than using create in beginning just use


SELECT tblFacility.vcFacilityID , COUNT(distinct tblMembers.vcMemberID)  AS reinstates
INTO #Reactivations
FROM vwReactivatedContracts AS vwReactivatedContracts LEFT OUTER JOIN
tblFacility AS tblFacility ON vwReactivatedContracts.vcHomeFacilityId = tblFacility.vcFacilityID INNER JOIN
tblMembers AS tblMembers ON vwReactivatedContracts.vcOwnerId = tblMembers.vcMemberID
WHERE (vwReactivatedContracts.reactivation_date = @Day) 
AND (vwReactivatedContracts.simembershipclassid in (Select * from #PrimaryClasses))
GROUP BY tblFacility.vcFacilityID

swims01
Yak Posting Veteran

59 Posts

Posted - 2009-05-08 : 13:03:29

Thanks visakh16!! Worked perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-08 : 13:08:15

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources