Multiple inserts from data in different tables

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

SQL Server Development (2000)

Multiple inserts from data in different tables

Author

Topic

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 12:57:49

I am trying to string together the two insert statements below. The first one populates 3 fields from one table and the second I would like to populate a 4th field using data from another table. The common ID among all 3 tables is WIS_ID (and WiS_ID_) The question is, can you do a second select within the same insert iteration and use it to populate?

TRUNCATE TABLE GISLOADER.CTY_FC_VACANCY
INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT)
SELECT
GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
max(GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID) AS OBJECTID,
COUNT(GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM
GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID

INSERT GISLOADER.CTY_FC_VACANCY (MAX_CHILD)
select GISLOADER.CTY_FC_PROVIDERS.MAXCHILDREN as MAX_CHILD,
FROM GISLOADER.CTY_FC_PROVIDERS JOIN GISLOADER.CTY_FC_VACANCY
ON (GISLOADER.CTY_FC_PROVIDERS.WIS_ID_ = GISLOADER.CTY_FC_VACANCY.WIS_ID)
GROUP BY Maxchildren

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 13:29:06

seems like what you need is this


TRUNCATE TABLE GISLOADER.CTY_FC_VACANCY
INSERT GISLOADER.CTY_FC_VACANCY (WIS_ID, OBJECTID, CHILDCOUNT,MAX_CHILD)
SELECT m.WIS_ID,m.OBJECTID,m.CHILDCOUNT,n.MAX_CHILD
FROM
(
SELECT 
GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID AS WIS_ID,
max(GISLOADER.CTY_FC_PROVIDERSRELATE.OBJECTID) AS OBJECTID,
COUNT(GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID) AS CHILDCOUNT
FROM 
GISLOADER.CTY_FC_PROVIDERSRELATE
GROUP BY GISLOADER.CTY_FC_PROVIDERSRELATE.WIS_ID
)m
JOIN (select DISTINCT GISLOADER.CTY_FC_VACANCY.WIS_ID,GISLOADER.CTY_FC_PROVIDERS.MAXCHILDREN as MAX_CHILD
FROM GISLOADER.CTY_FC_PROVIDERS JOIN GISLOADER.CTY_FC_VACANCY
ON (GISLOADER.CTY_FC_PROVIDERS.WIS_ID_ = GISLOADER.CTY_FC_VACANCY.WIS_ID)
)n
ON n.WIS_ID=m.WIS_ID

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 14:16:02

That leads to a message of (0 rows affected) and an empty table.

jlandwehr
Starting Member

23 Posts

Posted - 2009-03-17 : 15:30:43

Maybe there's a different way to explain what I'm trying to do. I'd like to calculate the Vacancy value to 'Y' in the CTY_FC_VACANCY table if the Childcount in that table is less than the MAXCHILDREN in the CTY_FC_Providers table. Is there an elegant way of doing this? I'd like to do it after the top part of the insert statement above.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources