ive made a couple assumptions but this should work:

CREATE TABLE #TEMP1

(

patId INTEGER NULL

, ancienPatId INTEGER NULL

)

PRINT 'Find patient with no phone number checked SMS - Phase 1'

INSERT INTO #temp1

SELECT DISTINCT

easilyPatient.pat_id

, easilyPatient.pat_ancien_id

FROM [DI628SW].[NOYAU_PATIENT].[noyau].[Patient] AS easilyPatient

WITH (NOLOCK)

LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT] AS easilyTelPatient

WITH (NOLOCK) ON easilyPatient.pat_id = easilyTelPatient.pat_id

LEFT JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE] AS easilyTel

WITH (NOLOCK) ON easilyTelPatient.tel_id = easilyTel.tel_id

WHERE (easilyTel.tel_envoi_sms = 0

OR easilyTel.[tel_envoi_sms] IS NULL)

AND easilyPatient.pat_ancien_id IS NOT NULL

SELECT COUNT(*)

FROM #TEMP1

--Select * from #Temp1

CREATE TABLE #TEMP2

(

cnetPatId INTEGER

, cnetNumTel VARCHAR(40)

, easilyTypeNumTelId INTEGER

, cnetnumeroPrincipal BIT NOT NULL

, cnetSMS INTEGER NOT NULL

, cnetCommentaire VARCHAR(40)

, easilyPatId INTEGER NULL

, Seen BIT DEFAULT 0

)

PRINT 'Matching PATIENT - PHASE 2'

INSERT INTO #temp2

SELECT CNet.pat_id

, LEFT(CNet.[NUM_TEL_REEL], 18) AS CNETNumTel

, CASE CNet.[TYPE_NUM_TEL_ID]

WHEN 1 THEN 1

WHEN 2 THEN 4

WHEN 3 THEN 3

WHEN 4 THEN 0

WHEN 5 THEN 5

END AS easilyTelCat

, CNet.[NUMERO_PRINCIPAL]

, CNet.[SMS]

, Cnet.[COMMENTAIRE]

, tmp1.patId

, 0

FROM [DI81SW].[CNET_EXP].[dbo].[NOYAU_NUM_TELEPHONE] AS CNet WITH (NOLOCK)

INNER JOIN #temp1 AS tmp1 ON tmp1.ancienPatId = CNet.pat_id

WHERE (CNet.[SMS] = 1)

AND (LEFT(CNet.[NUM_TEL_REEL], 18) NOT IN (

SELECT tel_numero COLLATE SQL_Latin1_General_CP1_CI_AS

FROM [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT]

AS TP WITH (NOLOCK)

INNER JOIN [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE]

AS T WITH (NOLOCK) ON TP.TEL_ID = T.TEL_ID

WHERE t.tel_envoi_sms = 1

AND tmp1.patid = TP.pat_id))

SELECT COUNT(*)

FROM #TEMP2

--Select * from #temp2

--DROP TABLE #TEMP1

--DROP TABLE #TEMP2

PRINT 'Insert missing phones - Phase 3'

DECLARE @cnetPatId INTEGER

DECLARE @cnetNumTel VARCHAR(40)

DECLARE @easilyTypeNumTelId INTEGER

DECLARE @cnetnumeroPrincipal BIT

DECLARE @cnetSMS BIT

DECLARE @easilyPatId INTEGER

DECLARE @cnetCommentaire VARCHAR(40)

DECLARE @bdEasilyTelephone VARCHAR(100)

DECLARE @bdEasilyTelephonePATIENT VARCHAR(100)

DECLARE @siteCode VARCHAR(10)

DECLARE @count INT

DECLARE @rowNumber INT

WHILE @count >= @rowNumber

BEGIN

SELECT TOP 1 @cnetPatId = etPatId

, @cnetNumTel = cnetNumTel

, @easilyTypeNumTelId = easilyTypeNumTelId

, @cnetnumeroPrincipal = cnetnumeroPrincipal

, @cnetSMS = cnetSMS

, @cnetCommentaire = cnetCommentaire

, @easilyPatId = easilyPatId

FROM #temp2

WHERE seen = 0

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE]

(tel_categorie

, tel_numero

, tel_envoi_sms

, tel_principal

, tel_commentaire

, tel_administratif

, tel_actif)

SELECT @easilyTypeNumTelId

, @cnetNumTel

, @cnetSMS

, @cnetnumeroPrincipal

, @cnetCommentaire

, 0

, 1

DECLARE @idTelephone INTEGER

SELECT @idTelephone = @@IDENTITY

INSERT INTO [DI628SW].[NOYAU_PATIENT].[noyau].[TELEPHONE_PATIENT]

(tel_id

, pat_id)

SELECT @idTelephone

, @easilyPatId

UPDATE #TEMP2 SET Seen = 1 WHERE etPatId =@cnetPatId -- Assumed this is the key

SET @rowNumber = @rowNumber+1

END

DROP TABLE #TEMP1

DROP TABLE #TEMP2

Also when you post code try using the IFCode Shortcuts on the side. this helps make it easier to read and pull out teh sql code from the post 🙂