CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedurestemp-tables
Minions
Minions

Reputation: 27

Incorrect data sequence inserted in stored procedure for using temporary table

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

Below is the scenario: this stored procedure 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 column in this table always starts with 1 every day.

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

I am using @contact and @trigger tables which are two table variables.

Data is manipulated in this stored procedure is finally entered into [dbo].[MergeTable] table.

An 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], somehow the temporary table @trigger gets a 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.

Data Flow:
Lets take a example data.

@trigger temp table
1 100
2 200
3 300

TriggerEmail table
1 AAA
2 BBB
3 CCC

Merge table will be a join between @trigger & TriggerEmail.
so it would look like this,
1 100 AAA
2 200 BBB
3 300 CCC

but my issue is, some times and only some times the data gets store in this way 1 100 BBB
2 200 AAA
3 300 CCC

Stored procedure:

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

Upvotes: 0

Views: 303

Answers (1)

James Z
James Z

Reputation: 12318

Not sure if this is the actual problem, but at least this looks like a bug to me:

You're inserting the rows into Contacts, and storing the new IDs into @contact:

  INSERT INTO [dbo].[Contacts] ...
  OUTPUT inserted.ID INTO @contact
  SELECT [EMailLogin], [FirstName], ....
  FROM [dbo].[TriggerEmailContent]
  ORDER BY Id

And after that, you're joining @contacts using the id from Contacts table with id from TriggerEmailContent, which isn't the same in case there is any IDs missing:

  INSERT INTO [dbo].[TriggerEmail] ...
  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

If the IDs are always the same and in same order, you don't need the whole @contact table at all.

Later you're doing the same thing with IDs from @trigger.

If you need to have some kind of ordering table variable, you have to store both the original id and the id in the new table into there and join that with the table, not the new id.

Update:

What I can see in the end is also similar ID mismatch problem with actual tables in the join TriggerEmail ste on ste.id = t.TriggerEmailId

What I would do to fix this, if I understood it properly, is insert the id from TriggerEmailContent into Contacts, TriggerEmail and MergeTable. That should then be used to join the data when it's actually fetched from tables. For ordering the data (if there actually is need for that) you can use the id field in Contacts (or one of the other tables) and order the data using it from that one source. This way I don't see any need for the table variables, which to me look like they are there just for trying to get same matching ID numbers in all those tables to be created in the insert.

The end by the way looks like you're doing an unpivot operation in the 10 inserts, so as far as I can see, you could just replace them with one insert using unpivot

Upvotes: 1

Related Questions