Generate Insert Script with If Not Exists

Question

I created a script for inserting to a table as follows using Task -> generate Script

GO
INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
VALUES ( N'Resource Booking', N'exceViewRptResourceBooking rb', N'rb.BookingId', N'BOK')
GO
INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
VALUES ( N'Freezed Contracts', N'exceViewRptFreeze fc', N'fc.FreezeItemId', N'FRZ')
GO
INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
VALUES ( N'Article', N'exceViewRptArticle art', N'art.ArticleId', N'ATL')
GO

but i want to do it as follows using scripting tool(because actual data set is large)

GO
if not exists (select * from USRPT.Entity where DisplayName = 'Resource Booking')
begin
    INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
    VALUES (N'Resource Booking', N'exceViewRptResourceBooking rb', N'rb.BookingId', N'BOK')
end

GO
if not exists (select * from USRPT.Entity where DisplayName = 'Freezed Contracts')
begin
    INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
    VALUES (N'Freezed Contracts', N'exceViewRptFreeze fc', N'fc.FreezeItemId', N'FRZ')
end
GO
if not exists (select * from USRPT.Entity where DisplayName = 'Article')
begin
    INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
    VALUES (N'Article', N'exceViewRptArticle art', N'art.ArticleId', N'ATL')
end

is there any way of doing this using SSMS or any other tool I want this ti save as a single .sql file and send to client.

Answer 1

This answer is based on the fact you have a pre-generated script you want to work with.

One way to achieve this based on a script that you have is to INSERT the values into a #TEMP table and then INSERT into the main table using a NOT EXISTS clause.

So first create a temp table above the code:

CREATE TABLE #temp ([DisplayName] nvarchar(max), 
                    [DataTableOrView] nvarchar(max), 
                    [PrimaryColumn] nvarchar(max), 
                    [BasedOn] nvarchar(max))

Then do a mass find and replace on all references of [USRPT].[Entity], replacing it with #temp. So your statements would all look like this:

INSERT #temp ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
VALUES ( N'Resource Booking', N'exceViewRptResourceBooking rb', N'rb.BookingId', N'BOK')

This will give you a #temp table containing all of the records you want to INSERT if they don't already exist.

Once that table is created, you can then perform an INSERT to the main table like so:

INSERT [USRPT].[Entity] ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn])
SELECT [DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]
FROM #temp
WHERE  NOT EXISTS ( SELECT DisplayName 
                    FROM [USRPT].[Entity] m
                    WHERE  m.DisplayName = #temp.DisplayName )

So it will INSERT records that do not already exist.

A simplified example that you can run to test is below:

CREATE TABLE #master ( val VARCHAR(10) )

INSERT  INTO #master
        ( val )
VALUES  ( 'abc' ),
        ( 'def' ),
        ( 'ghi' ),
        ( 'jkl' ),
        ( 'mno' ),
        ( 'pqr' )

SELECT  val AS OriginalValues
FROM    #master

CREATE TABLE #temp ( val VARCHAR(10) )

INSERT  INTO #temp
        ( val )
VALUES  ( 'abc' ),
        ( 'def' ),
        ( 'stu' ),
        ( 'xyz' )

SELECT  val AS ValuesToBeInserted
FROM    #temp

INSERT  INTO #master
        ( val
        )
        SELECT  val
        FROM    #temp
        WHERE   NOT EXISTS ( SELECT val
                             FROM   #master m
                             WHERE  m.val = #temp.val )

SELECT val AS MergedValues 
FROM #master
ORDER BY val

DROP TABLE #master
DROP TABLE #temp

Answer 2

You can use MERGE to modify the Entity table by updating and inserting rows. When the value of [DisplayName] in the source table matches a value in the [DisplayName] column of the target table, (Entity), all the columns are updated in the target table. When the value of [DisplayName] does not match, the source row is inserted into the target table. The source table is a derived table that uses the Transact-SQL table value constructor to specify multiple rows for the source table.

DECLARE Entity TABLE ([DisplayName] nvarchar(200),
                              [DataTableOrView] nvarchar(200),
                              [PrimaryColumn] nvarchar(200),
                              [BasedOn] nvarchar(200)); 

INSERT into @Entity ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn]) 
VALUES ( N'Resource Booking', N'Test rb', N'Test', N'Test')

MERGE into Entity AS target     
    USING (VALUES (N'Resource Booking', N'exceViewRptResourceBooking rb', N'rb.BookingId', N'BOK'),
                  (N'Freezed Contracts', N'exceViewRptFreeze fc', N'fc.FreezeItemId', N'FRZ'),
                  (N'Article', N'exceViewRptArticle art', N'art.ArticleId', N'ATL'))
    AS source ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn])
    ON (target.[DisplayName] = source.[DisplayName])
    WHEN MATCHED THEN 
        UPDATE SET [DataTableOrView] = source.[DataTableOrView],
                   [PrimaryColumn] = source.[PrimaryColumn],
                   [BasedOn] = source.[BasedOn]
    WHEN NOT MATCHED THEN
    INSERT ([DisplayName], [DataTableOrView], [PrimaryColumn], [BasedOn])
    VALUES (source.[DisplayName], source.[DataTableOrView], source.[PrimaryColumn], source.[BasedOn] );