sql: Incorrect data sequence inserted in stored procedure for using temporary table

I am using temporary tables in below stored procedure and it is creating me lot of problems.

Below is the scenario:
This sp is run once in a day and pick and process entry’s for that day.

[dbo].[TriggerEmailContent] is a permanent table, where data is entered into it through some other process. But every day data is wipped out from this table.
So ‘id’ field in this table always start with 1 every day.

[dbo].[Contacts] and [dbo].[TriggerEmail] are also permanent tables.

i am using @contact and @trigger tables which are two temporary tables.

Data is manipulated in this stored procedure is finally entered into [dbo].[MergeTable] table.
A email server picks this data from this table order by TriggerEmailId and send email to customers.

The issue i am facing is,
when entering data into [dbo].[MergeTable], some how the temporary table @trigger have wrong sequence of data, due to which data entered in [dbo].[MergeTable] for that instance is stored as wrong.

The issue is only with temporary tables, All permanent tables have correct data.

What i want,
How to restructure the sp so i can get ride of @trigger table?
How to make sure data entered in [dbo].[MergeTable] is correct data from [dbo].[TriggerEmailContent] table.

Stored procedure below,

USE [ServerA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

— =============================================
ALTER PROCEDURE [dbo].[DatabaseA]
@currentDate as datetime
AS
BEGIN
DECLARE @contact TABLE (Id int IDENTITY(1,1), ContactId int)
DECLARE @trigger TABLE (Id int IDENTITY(1,1), TriggerEmailId int)

— Create the Contact Lines for ServerA

  INSERT INTO [dbo].[Contacts] ([EMailLogin],[FirstName],[LastName],[SendText],[SendHTML],[bounceCount],[verified],
  [Title], [CompanyName], [Address1], [Address2], [Address3], [Postcode], [Phone], [Country], [Region], [Department],
  [LanguageCode], [Aemail], [DaysToExpiry], [SubjectLine], [xxxxPhone], [xxxxAddress],
  [xxxxCompany])
  OUTPUT inserted.ID INTO @contact
  SELECT [EMailLogin], [FirstName],[LastName],1,1,0,0,[Title], [CompanyName], [Address1], [Address2], [Address3], [Postcode], [Phone], 
  [Country], [Region], [Department], [LanguageCode], [Aemail], [DaysToExpiry], [SubjectLine], [xxxxPhone], 
  [xxxxAddress], [xxxxCompany]
  FROM [dbo].[TriggerEmailContent]
  ORDER BY Id

— Create TriggerEmail lines

  INSERT INTO [dbo].[TriggerEmail]
       ([MergeId],[ContactId],[sendDate],[sendStatus], [isActive])
  OUTPUT inserted.ID INTO @trigger
 SELECT te.[MergeId], t.[ContactId], @currentDate, 0, 1
  FROM [dbo].[TriggerEmailContent] as te, @contact as t
  WHERE te.Id = t.Id
  ORDER BY t.Id

— Add All Mergefield
INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
SELECT te.[MergeId], 2, te.[MergeField02], t.[TriggerEmailId]
FROM [dbo].[TriggerEmailContent] te
join @trigger t on te.Id = t.Id
join TriggerEmail ste on ste.id = t.TriggerEmailId

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 2, te.[MergeField02],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 3, te.[MergeField03],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 4, te.[MergeField04],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 5, te.[MergeField05],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 6, te.[MergeField06],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 7, te.[MergeField07],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 8, te.[MergeField08],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 9, te.[MergeField09],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

  INSERT INTO [dbo].[MergeTable] ([MergeId],[MergeFieldNumber],[MergeFieldContent],[TriggerEmailId])
 SELECT te.[MergeId], 10, te.[MergeField10],  t.[TriggerEmailId]
  FROM [dbo].[TriggerEmailContent] as te, @trigger as t
  WHERE te.Id = t.Id      

END


Source: mysql

Leave a Reply