CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Eric Aime Tchatchoua
Eric Aime Tchatchoua

Reputation: 191

SQL query to stop duplicate ID insert in a table using SQL Server

I am trying to stop duplicate MessageId inserted into the TestMobileRecipient table. At the moment all section of the sql query work fine except "Insert Mobile detail into the Email Recipient table" section at the moment when the store procedure is run more than once I have duplicate messageid in TestMobileRecipient table. Any suggestion how to resolve this issue

 Create PROCEDURE [dbo].[ImportMessage]
 AS
 BEGIN
 --- Insert Message details in the Email table
  INSERT INTO TestTextMessage (MessageId, Origin, MessageBody, 
       MessageSent, DateCreated, DateSent,DontSendBefore) 
  SELECT Id, Origin, MessageBody,MessageSent,DateCreated,DateSent ,DontSendBefore
  FROM TextMessage
  WHERE 
      DontSendBefore <= GETDate()  and  DateSent is null and MessageSent = 0

--- Only run update when the query above return a value
    IF @@ROWCOUNT > 0 

--- Update TextMessage Table after the data is insert in the Email table
 UPDATE TextMessage 
     SET DateSent= GETDate(),
     DontSendBefore = null,
     MessageSent = 1
  WHERE
    Id= (select MessageId 
  FROM 
    TestTextMessage
  WHERE 
     TextMessage.id= TestTextMessage.MessageId)

 -- Insert Mobile detail into the Email Recipient table

   INSERT Into TestMobileRecipient(MessageId, MobileNumber)
   SELECT
      MessageId, MobileNumber
   FROM 
      MobileRecipient
   WHERE
      MessageId =(SELECT   MessageId 
   FROM   
       TestTextMessage
   WHERE
      MobileRecipient.MessageId= TestTextMessage.MessageId)
END

Upvotes: 0

Views: 169

Answers (1)

Aura
Aura

Reputation: 1307

You can add a WHERE clause like following, that verifies if the MessageId already exists in the TestMobileRecipient table:

INSERT INTO TestMobileRecipient
    (
        MessageId
    )
            SELECT
                MessageId,
                MobileNumber
            FROM
                MobileRecipient
            WHERE
                MessageId =
                (
                    SELECT
                        MessageId
                    FROM
                        TestTextMessage
                    WHERE
                        MobileRecipient.MessageId = TestTextMessage.MessageId
                )
                AND NOT EXISTS
                (
                    SELECT
                        MessageId
                    FROM
                        TestMobileRecipient tmr2
                    WHERE
                       tmr2.MessageId = MobileRecipient.MessageId
                );

Or if you would like to get a warning while inserting a duplicate value, you can create an index like following:

CREATE UNIQUE NONCLUSTERED INDEX [IndexTestMobileRecipient] ON TestMobileRecipient 
(
    MessageId

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = ON, 
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

Upvotes: 1

Related Questions