quote:

---

Select @callid=Callid
FROM inserted WHERE
isnull(inserted.SITES,'') > ''

---

CREATE TRIGGER	dbo.SITE_UPDATE
ON		dbo.CallLog
FOR		INSERT,
		UPDATE
AS

SET NOCOUNT ON

DECLARE	@Stage TABLE
	(
		CallID INT,
		[Name] VARCHAR(20),
		[Rating] INT
	)

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, Clifton01N, Clifton01R FROM inserted WHERE Clifton01Y = 'X' AND Clifton01N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton02N, Clifton02R FROM inserted WHERE Clifton02Y = 'X' AND Clifton02N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton03N, Clifton03R FROM inserted WHERE Clifton03Y = 'X' AND Clifton03N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton04N, Clifton04R FROM inserted WHERE Clifton04Y = 'X' AND Clifton04N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton05N, Clifton05R FROM inserted WHERE Clifton05Y = 'X' AND Clifton05N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton06N, Clifton06R FROM inserted WHERE Clifton06Y = 'X' AND Clifton06N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton07N, Clifton07R FROM inserted WHERE Clifton07Y = 'X' AND Clifton07N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton08N, Clifton08R FROM inserted WHERE Clifton08Y = 'X' AND Clifton08N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton09N, Clifton09R FROM inserted WHERE Clifton09Y = 'X' AND Clifton09N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton10N, Clifton10R FROM inserted WHERE Clifton10Y = 'X' AND Clifton10N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton11N, Clifton11R FROM inserted WHERE Clifton11Y = 'X' AND Clifton11N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton12N, Clifton12R FROM inserted WHERE Clifton12Y = 'X' AND Clifton12N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton13N, Clifton13R FROM inserted WHERE Clifton13Y = 'X' AND Clifton13N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton14N, Clifton14R FROM inserted WHERE Clifton14Y = 'X' AND Clifton14N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton15N, Clifton15R FROM inserted WHERE Clifton15Y = 'X' AND Clifton15N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton16N, Clifton16R FROM inserted WHERE Clifton16Y = 'X' AND Clifton16N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton17N, Clifton17R FROM inserted WHERE Clifton17Y = 'X' AND Clifton17N > '' AND Sites > '' UNION ALL
SELECT	CallID, Clifton18N, Clifton18R FROM inserted WHERE Clifton18Y = 'X' AND Clifton18N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, Frenchay01N, Frenchay01R FROM inserted WHERE Frenchay01Y = 'X' AND Frenchay01N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay02N, Frenchay02R FROM inserted WHERE Frenchay02Y = 'X' AND Frenchay02N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay03N, Frenchay03R FROM inserted WHERE Frenchay03Y = 'X' AND Frenchay03N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay04N, Frenchay04R FROM inserted WHERE Frenchay04Y = 'X' AND Frenchay04N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay05N, Frenchay05R FROM inserted WHERE Frenchay05Y = 'X' AND Frenchay05N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay06N, Frenchay06R FROM inserted WHERE Frenchay06Y = 'X' AND Frenchay06N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay07N, Frenchay07R FROM inserted WHERE Frenchay07Y = 'X' AND Frenchay07N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay08N, Frenchay08R FROM inserted WHERE Frenchay08Y = 'X' AND Frenchay08N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay09N, Frenchay09R FROM inserted WHERE Frenchay09Y = 'X' AND Frenchay09N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay10N, Frenchay10R FROM inserted WHERE Frenchay10Y = 'X' AND Frenchay10N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay11N, Frenchay11R FROM inserted WHERE Frenchay11Y = 'X' AND Frenchay11N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay12N, Frenchay12R FROM inserted WHERE Frenchay12Y = 'X' AND Frenchay12N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay13N, Frenchay13R FROM inserted WHERE Frenchay13Y = 'X' AND Frenchay13N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay14N, Frenchay14R FROM inserted WHERE Frenchay14Y = 'X' AND Frenchay14N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay15N, Frenchay15R FROM inserted WHERE Frenchay15Y = 'X' AND Frenchay15N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay16N, Frenchay16R FROM inserted WHERE Frenchay16Y = 'X' AND Frenchay16N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay17N, Frenchay17R FROM inserted WHERE Frenchay17Y = 'X' AND Frenchay17N > '' AND Sites > '' UNION ALL
SELECT	CallID, Frenchay18N, Frenchay18R FROM inserted WHERE Frenchay18Y = 'X' AND Frenchay18N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, ExeUni01N, ExeUni01R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni01N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni02N, ExeUni02R FROM inserted WHERE ExeUni02Y = 'X' AND ExeUni02N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni03N, ExeUni03R FROM inserted WHERE ExeUni03Y = 'X' AND ExeUni03N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni04N, ExeUni04R FROM inserted WHERE ExeUni04Y = 'X' AND ExeUni04N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni05N, ExeUni05R FROM inserted WHERE ExeUni05Y = 'X' AND ExeUni05N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni06N, ExeUni06R FROM inserted WHERE ExeUni06Y = 'X' AND ExeUni06N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni07N, ExeUni07R FROM inserted WHERE ExeUni07Y = 'X' AND ExeUni07N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni08N, ExeUni08R FROM inserted WHERE ExeUni08Y = 'X' AND ExeUni08N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni09N, ExeUni09R FROM inserted WHERE ExeUni09Y = 'X' AND ExeUni09N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni10N, ExeUni10R FROM inserted WHERE ExeUni10Y = 'X' AND ExeUni10N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni11N, ExeUni11R FROM inserted WHERE ExeUni11Y = 'X' AND ExeUni11N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni12N, ExeUni12R FROM inserted WHERE ExeUni12Y = 'X' AND ExeUni12N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni13N, ExeUni13R FROM inserted WHERE ExeUni13Y = 'X' AND ExeUni13N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni14N, ExeUni14R FROM inserted WHERE ExeUni14Y = 'X' AND ExeUni14N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni15N, ExeUni15R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni15N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni16N, ExeUni16R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni16N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni17N, ExeUni17R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni17N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeUni18N, ExeUni18R FROM inserted WHERE ExeUni01Y = 'X' AND ExeUni18N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, BathCD01N, BathCD01R FROM inserted WHERE BathCD01Y = 'X' AND BathCD01N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathCD02N, BathCD02R FROM inserted WHERE BathCD02Y = 'X' AND BathCD02N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathCD03N, BathCD03R FROM inserted WHERE BathCD03Y = 'X' AND BathCD03N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathCD04N, BathCD04R FROM inserted WHERE BathCD04Y = 'X' AND BathCD04N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathCD05N, BathCD05R FROM inserted WHERE BathCD05Y = 'X' AND BathCD05N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathCD06N, BathCD06R FROM inserted WHERE BathCD06Y = 'X' AND BathCD06N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, BathJW01N, BathJW01R FROM inserted WHERE BathJW01Y = 'X' AND BathJW01N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathJW02N, BathJW02R FROM inserted WHERE BathJW02Y = 'X' AND BathJW02N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathJW03N, BathJW03R FROM inserted WHERE BathJW03Y = 'X' AND BathJW03N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathJW04N, BathJW04R FROM inserted WHERE BathJW04Y = 'X' AND BathJW04N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathJW05N, BathJW05R FROM inserted WHERE BathJW05Y = 'X' AND BathJW05N > '' AND Sites > '' UNION ALL
SELECT	CallID, BathJW06N, BathJW06R FROM inserted WHERE BathJW06Y = 'X' AND BathJW06N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, CheltUoG01X, CheltUoG01R FROM inserted WHERE CheltUoG01 = 'X' AND CheltUoG01X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG02X, CheltUoG02R FROM inserted WHERE CheltUoG02 = 'X' AND CheltUoG02X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG03X, CheltUoG03R FROM inserted WHERE CheltUoG03 = 'X' AND CheltUoG03X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG04X, CheltUoG04R FROM inserted WHERE CheltUoG04 = 'X' AND CheltUoG04X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG05X, CheltUoG05R FROM inserted WHERE CheltUoG05 = 'X' AND CheltUoG05X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG06X, CheltUoG06R FROM inserted WHERE CheltUoG06 = 'X' AND CheltUoG06X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG07X, CheltUoG07R FROM inserted WHERE CheltUoG07 = 'X' AND CheltUoG07X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG08X, CheltUoG08R FROM inserted WHERE CheltUoG08 = 'X' AND CheltUoG08X > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltUoG09X, CheltUoG09R FROM inserted WHERE CheltUoG09 = 'X' AND CheltUoG09X > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, SCAT01N, SCAT01R FROM inserted WHERE SCAT01Y = 'X' AND SCAT01N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT02N, SCAT02R FROM inserted WHERE SCAT02Y = 'X' AND SCAT02N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT03N, SCAT03R FROM inserted WHERE SCAT03Y = 'X' AND SCAT03N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT04N, SCAT04R FROM inserted WHERE SCAT04Y = 'X' AND SCAT04N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT05N, SCAT05R FROM inserted WHERE SCAT05Y = 'X' AND SCAT05N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT06N, SCAT06R FROM inserted WHERE SCAT06Y = 'X' AND SCAT06N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT07N, SCAT07R FROM inserted WHERE SCAT07Y = 'X' AND SCAT07N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT08N, SCAT08R FROM inserted WHERE SCAT08Y = 'X' AND SCAT08N > '' AND Sites > '' UNION ALL
SELECT	CallID, SCAT09N, SCAT09R FROM inserted WHERE SCAT09Y = 'X' AND SCAT09N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, CheltglosCol01N, CheltglosCol01R FROM inserted WHERE CheltglosCol01Y = 'X' AND CheltglosCol01N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol02N, CheltglosCol02R FROM inserted WHERE CheltglosCol02Y = 'X' AND CheltglosCol02N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol03N, CheltglosCol03R FROM inserted WHERE CheltglosCol03Y = 'X' AND CheltglosCol03N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol04N, CheltglosCol04R FROM inserted WHERE CheltglosCol04Y = 'X' AND CheltglosCol04N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol05N, CheltglosCol05R FROM inserted WHERE CheltglosCol05Y = 'X' AND CheltglosCol05N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol06N, CheltglosCol06R FROM inserted WHERE CheltglosCol06Y = 'X' AND CheltglosCol06N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol07N, CheltglosCol07R FROM inserted WHERE CheltglosCol07Y = 'X' AND CheltglosCol07N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol08N, CheltglosCol08R FROM inserted WHERE CheltglosCol08Y = 'X' AND CheltglosCol08N > '' AND Sites > '' UNION ALL
SELECT	CallID, CheltglosCol09N, CheltglosCol09R FROM inserted WHERE CheltglosCol09Y = 'X' AND CheltglosCol09N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, SponsCon01N, SponsCon01R FROM inserted WHERE SponsCon01Y = 'X' AND SponsCon01N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon02N, SponsCon02R FROM inserted WHERE SponsCon02Y = 'X' AND SponsCon02N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon03N, SponsCon03R FROM inserted WHERE SponsCon03Y = 'X' AND SponsCon03N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon04N, SponsCon04R FROM inserted WHERE SponsCon04Y = 'X' AND SponsCon04N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon05N, SponsCon05R FROM inserted WHERE SponsCon05Y = 'X' AND SponsCon05N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon06N, SponsCon06R FROM inserted WHERE SponsCon06Y = 'X' AND SponsCon06N > '' AND Sites > '' UNION ALL
SELECT	CallID, SponsCon07N, SponsCon07R FROM inserted WHERE SponsCon07Y = 'X' AND SponsCon07N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, ClinAcad01N, ClinAcad01R FROM inserted WHERE ClinAcad01Y = 'X' AND ClinAcad01N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad02N, ClinAcad02R FROM inserted WHERE ClinAcad02Y = 'X' AND ClinAcad02N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad03N, ClinAcad03R FROM inserted WHERE ClinAcad03Y = 'X' AND ClinAcad03N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad04N, ClinAcad04R FROM inserted WHERE ClinAcad04Y = 'X' AND ClinAcad04N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad05N, ClinAcad05R FROM inserted WHERE ClinAcad05Y = 'X' AND ClinAcad05N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad06N, ClinAcad06R FROM inserted WHERE ClinAcad06Y = 'X' AND ClinAcad06N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad07N, ClinAcad07R FROM inserted WHERE ClinAcad07Y = 'X' AND ClinAcad07N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad08N, ClinAcad08R FROM inserted WHERE ClinAcad08Y = 'X' AND ClinAcad08N > '' AND Sites > '' UNION ALL
SELECT	CallID, ClinAcad09N, ClinAcad09R FROM inserted WHERE ClinAcad09Y = 'X' AND ClinAcad09N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, ExeCol01N, ExeCol01R FROM inserted WHERE ExeCol01Y = 'X' AND ExeCol01N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeCol02N, ExeCol02R FROM inserted WHERE ExeCol02Y = 'X' AND ExeCol02N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeCol03N, ExeCol03R FROM inserted WHERE ExeCol03Y = 'X' AND ExeCol03N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeCol04N, ExeCol04R FROM inserted WHERE ExeCol04Y = 'X' AND ExeCol04N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeCol05N, ExeCol05R FROM inserted WHERE ExeCol05Y = 'X' AND ExeCol05N > '' AND Sites > '' UNION ALL
SELECT	CallID, ExeCol06N, ExeCol06R FROM inserted WHERE ExeCol06Y = 'X' AND ExeCol06N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, PlymCity01N, PlymCity01R FROM inserted WHERE PlymCity01Y = 'X' AND PlymCity01N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymCity02N, PlymCity02R FROM inserted WHERE PlymCity02Y = 'X' AND PlymCity02N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymCity03N, PlymCity03R FROM inserted WHERE PlymCity03Y = 'X' AND PlymCity03N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymCity04N, PlymCity04R FROM inserted WHERE PlymCity04Y = 'X' AND PlymCity04N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymCity05N, PlymCity05R FROM inserted WHERE PlymCity05Y = 'X' AND PlymCity05N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymCity06N, PlymCity06R FROM inserted WHERE PlymCity06Y = 'X' AND PlymCity06N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, PlymUni01N, PlymUni01R FROM inserted WHERE PlymUni01Y = 'X' AND PlymUni01N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymUni02N, PlymUni02R FROM inserted WHERE PlymUni02Y = 'X' AND PlymUni02N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymUni03N, PlymUni03R FROM inserted WHERE PlymUni03Y = 'X' AND PlymUni03N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymUni04N, PlymUni04R FROM inserted WHERE PlymUni04Y = 'X' AND PlymUni04N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymUni05N, PlymUni05R FROM inserted WHERE PlymUni05Y = 'X' AND PlymUni05N > '' AND Sites > '' UNION ALL
SELECT	CallID, PlymUni06N, PlymUni06R FROM inserted WHERE PlymUni06Y = 'X' AND PlymUni06N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, BridCol01N, BridCol01R FROM inserted WHERE BridCol01Y = 'X' AND BridCol01N > '' AND Sites > '' UNION ALL
SELECT	CallID, BridCol02N, BridCol02R FROM inserted WHERE BridCol02Y = 'X' AND BridCol02N > '' AND Sites > '' UNION ALL
SELECT	CallID, BridCol03N, BridCol03R FROM inserted WHERE BridCol03Y = 'X' AND BridCol03N > '' AND Sites > '' UNION ALL
SELECT	CallID, BridCol04N, BridCol04R FROM inserted WHERE BridCol04Y = 'X' AND BridCol04N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, RAC01N, RAC01R FROM inserted WHERE RAC01Y = 'X' AND RAC01N > '' AND Sites > '' UNION ALL
SELECT	CallID, RAC02N, RAC02R FROM inserted WHERE RAC02Y = 'X' AND RAC02N > '' AND Sites > '' UNION ALL
SELECT	CallID, RAC03N, RAC03R FROM inserted WHERE RAC03Y = 'X' AND RAC03N > '' AND Sites > '' UNION ALL
SELECT	CallID, RAC04N, RAC04R FROM inserted WHERE RAC04Y = 'X' AND RAC04N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, CIRENCESTER01N, CIRENCESTER01R FROM inserted WHERE CIRENCESTER01Y = 'X' AND CIRENCESTER01N > '' AND Sites > '' UNION ALL
SELECT	CallID, CIRENCESTER02N, CIRENCESTER02R FROM inserted WHERE CIRENCESTER02Y = 'X' AND CIRENCESTER02N > '' AND Sites > '' UNION ALL
SELECT	CallID, CIRENCESTER03N, CIRENCESTER03R FROM inserted WHERE CIRENCESTER03Y = 'X' AND CIRENCESTER03N > '' AND Sites > '' UNION ALL
SELECT	CallID, CIRENCESTER04N, CIRENCESTER04R FROM inserted WHERE CIRENCESTER04Y = 'X' AND CIRENCESTER04N > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, POP101X, POP101R FROM inserted WHERE POP101Y = 'X' AND POP101X > '' AND Sites > '' UNION ALL
SELECT	CallID, POP102X, POP102R FROM inserted WHERE POP102Y = 'X' AND POP102X > '' AND Sites > ''

INSERT	@Stage (CallID, [Name], [Rating])
SELECT	CallID, POP201N, POP201R FROM inserted WHERE POP201Y = 'X' AND POP201N > '' AND Sites > '' UNION ALL
SELECT	CallID, POP202N, POP202R FROM inserted WHERE POP202Y = 'X' AND POP202N > '' AND Sites > ''

DECLARE	@Formatting TABLE
	(
		CallID INT,
		CircuitName VARCHAR(20)
	)

INSERT		@Formatting (CallID, CircuitName)
SELECT DISTINCT	s.CallID
FROM		@Stage AS s
INNER JOIN	dbo.Circuit AS c ON c.CircuitDesc = s.[Name]
			AND c.CircuitRole = s.[Rating]

UPDATE		cl
SET		cl.Sites = STUFF(s.SiteNames, 1, 2, '')
FROM		dbo.CallLog AS cl
CROSS APPLY	(
			SELECT	', ' + x.CircuitName
			FROM	@Formatting AS x
			WHERE	x.CallID = cl.CallID
			FOR XML	PATH('')
		) AS f(SiteNames)
WHERE		EXISTS (SELECT * FROM @Formatting AS y WHERE y.CallID = cl.CallID)

quote:

---

Originally posted by icw

HI Peso,

firstly thanks a bunch for this.
I just pasted your code
But am getting the following error when I try to parse your code.
Do you know why?

Msg 120, Level 15, State 1, Procedure SITE_UPDATE, Line 201
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.

---

DECLARE	@Formatting TABLE
	(
		CallID INT,
		CircuitName VARCHAR(20)
	)

INSERT		@Formatting (CallID, CircuitName)
SELECT DISTINCT	s.CallID,column containing CircuitName here
FROM		@Stage AS s
INNER JOIN	dbo.Circuit AS c ON c.CircuitDesc = s.[Name]
			AND c.CircuitRole = s.[Rating]

UPDATE		cl
SET		cl.Sites = STUFF(s.SiteNames, 1, 2, '')
FROM		dbo.CallLog AS cl
CROSS APPLY	(
			SELECT	', ' + x.CircuitName
			FROM	@Formatting AS x
			WHERE	x.CallID = cl.CallID
			FOR XML	PATH('')
		) AS f(SiteNames)
WHERE		EXISTS (SELECT * FROM @Formatting AS y WHERE y.CallID = cl.CallID)