0

I am trying to insert multiple records (~250) in a table (say MyTable) and would like to insert a new row only if it does not exist already.

I am using SQL Server 2008 R2 and got help from other threads like SQL conditional insert if row doesn't already exist.

While I am able to achieve that with following stripped script, I would like to know if there is a better (short) way to do this as I have to repeat this checking for every row inserted. Since we need to execute this script only once during DB deployment, I am not too much worried about performance.

INSERT INTO MyTable([Description], [CreatedDate], [CreatedBy], [ModifiedDate], [ModifiedBy], [IsActive], [IsDeleted]) 

SELECT N'ababab', GETDATE(), 1, NULL, NULL, 1, 0
WHERE NOT EXISTS(SELECT * FROM MyTable WITH (ROWLOCK, HOLDLOCK, UPDLOCK)
   WHERE
      ([InstanceId] IS NULL OR  [InstanceId] = 1)
      AND [ChannelPartnerId] IS NULL
      AND [CreatedBy] = 1) 
UNION ALL

SELECT N'xyz', 1, GETDATE(), 1, NULL, NULL, 1, 0 
WHERE NOT EXISTS(SELECT * FROM [dbo].[TemplateQualifierCategoryMyTest] WITH (ROWLOCK, HOLDLOCK, UPDLOCK)
   WHERE
      ([InstanceId] IS NULL OR  [InstanceId] = 1)
      AND [ChannelPartnerId] IS NULL
      AND [CreatedBy] = 1)

-- More SELECT statements goes here
Community
  • 1
  • 1
SBirthare
  • 5,117
  • 4
  • 34
  • 59
  • Have you tried to use MERGE ? http://technet.microsoft.com/en-us/library/bb510625.aspx – A Hocevar Mar 22 '13 at 08:32
  • Can you use the [MERGE](http://technet.microsoft.com/en-us/library/bb510625.aspx) instead of the Insert? hahaha @AHocevar beat me to it. – Preet Sangha Mar 22 '13 at 08:32
  • Thanks for the suggestion, I have not tried MERGE yet. First impression of MERGE, does it require two tables? Need to figure out how to fit it in my case (hard coded values going into one table). Do you think MERGE can be used in this scenario? – SBirthare Mar 22 '13 at 08:39
  • MERGE can be used with any number of tables, even one; you shouldn't have any troubles in your case. – A Hocevar Mar 22 '13 at 08:47
  • Got it, its working with MERGE also however again the lines of code remain same. What I am interested to know is can I reuse the where clause like function or something to shorten the script... anyway thanks for response. Will continue my search... – SBirthare Mar 22 '13 at 09:07
  • Another problem with MERGE in this case seems to be, inability to use UNION ALL. How can I insert more than one row in WHEN NOT MATCHED THEN clause. – SBirthare Mar 22 '13 at 09:16

1 Answers1

0

You could create a temporary table with your descriptions, then insert them all into the MyTable with a select that will check for rows in the temporary table that is not yet present in your destination, (this trick in implemented by the LEFT OUTER JOIN in conjunction with the IS NULL for the MyTable.Description part in the WHERE-Clause):

DECLARE @Descriptions TABLE ([Description] VARCHAR(200) NOT NULL )

INSERT INTO @Descriptions ( Description )VALUES  ( 'ababab' )
INSERT INTO @Descriptions ( Description )VALUES  ( 'xyz' )

INSERT INTO dbo.MyTable
        ( Description ,
          CreatedDate ,
          CreatedBy ,
          ModifiedDate ,
          ModifiedBy ,
          IsActive ,
          IsDeleted
        )

SELECT d.Description, GETDATE(), 1, NULL, NULL, 1, 0
FROM @Descriptions d
LEFT OUTER JOIN dbo.MyTable mt ON d.Description = mt.Description
WHERE mt.Description IS NULL
Elken
  • 172
  • 1
  • 4