How to insert multiple rows into SQL using an Array?

Question

I want to insert multiple rows into a database if they don't already exist, swapping out one columns value for each insertion. Below is some pseudo code of the goal:

IF NOT EXISTS (SELECT [ChangeReason] FROM BudgetAndAuthorizationChangeReasons WHERE [ChangeReason]= ('Other','Scope Change'))
INSERT INTO [dbo].[BudgetAndAuthorizationChangeReasons]
        ([ChangeReason]
        ,[IsActive]
        ,[CreatedByUser]
        ,[CreatedOn]
        ,[LastUpdatedByUser]
        ,[LastUpdatedOn])
     VALUES
        ( ('Other','Scope Change')      
        ,'true'
        ,'system'
        ,GETDATE()
        ,null
        ,null)
GO

In other words, if a row with ChangeReason='Other' already exists, nothing will happen. If it does not exist, then it will be inserted with all the other values outlined as above (IsActive=true, etc). This will be repeated for each element in the array : ('Other','Scope Change')

EDIT:

I have written a stored procedure to take care of the dirty work for me. Is it possible to call this automatically for every element in the array? Or do I need X different exec statements?

IF EXISTS ( select * from sys.procedures where name='SafeInsert_BudgetAndAuthorizationChangeReasons') begin
    DROP PROC SafeInsert_BudgetAndAuthorizationChangeReasons
end;
GO
create procedure SafeInsert_BudgetAndAuthorizationChangeReasons @Reason varchar(50)
as
begin
        IF NOT EXISTS (SELECT [ChangeReason] FROM BudgetAndAuthorizationChangeReasons WHERE [ChangeReason]= @Reason)
        INSERT INTO [dbo].[BudgetAndAuthorizationChangeReasons]
                ([ChangeReason]
                ,[IsActive]
                ,[CreatedByUser]
                ,[CreatedOn]
                ,[LastUpdatedByUser]
                ,[LastUpdatedOn])
             VALUES
                (@Reason        
                ,'true'
                ,'system'
                ,GETDATE()
                ,null
                ,null);
end;
GO

exec SafeInsert_BudgetAndAuthorizationChangeReasons @Reason='Other';
-- goal: auto-exec for every element in ('Other','Scope Change')

Are you sure about `[ChangeReason]= ('Other','Scope Change')`? As it is, it's unclear what are you trying to do — Ilyes, Sep 18 '19 at 18:32
Sami: no it is just pseudo code. Gordon: The column structure is outlined in the attempted insertion. I updated with more info about desired results. — James L., Sep 18 '19 at 18:34
Possible duplicate of [SQL Server Insert if not exist](https://stackoverflow.com/questions/20971680/sql-server-insert-if-not-exist) — Ilyes, Sep 18 '19 at 18:37
It is not a duplicate: that link only inserts 1 row whereas I want to insert multiple rows. — James L., Sep 18 '19 at 18:38
So, you're trying to insert two rows having the values `'Other'` and `'Scope Change'` if those two values doesn't exists in your table? @JamesL. If so, then it's very simple, just `INSERT` the data from the results of `RIGHT/LEFT` joining your table with the values and filter by `NULL` — Ilyes, Sep 18 '19 at 18:50
I think you should read that other question more carefully Sami. Your solution might work but I'm not sure of the performance implications and the maintainability of something like that. I wrote a stored procedure and updated the question about how to use it with the Array portion — James L., Sep 18 '19 at 19:07

score 2 · Answer 1 · edited Sep 18 '19 at 19:34

2

SQL doesn't understand arrays, but you can list multiple insert values like below.

INSERT INTO [dbo].[BudgetAndAuthorizationChangeReasons]
        ([ChangeReason]
        ,[IsActive]
        ,[CreatedByUser]
        ,[CreatedOn]
        ,[LastUpdatedByUser]
        ,[LastUpdatedOn])
     VALUES 
        ('Other'        , 'true', 'system', GETDATE(), null, null)
       ,('Scope Change' , 'true', 'system', GETDATE(), null, null)

edited Sep 18 '19 at 19:34

Ilyes

14,126
4
21
49

answered Sep 18 '19 at 18:49

nosnevel

107
7

1

Again, OP stated in his question _"In other words, if a row with ChangeReason='Other' already exists, nothing will happen. If it does not exist, then it will be inserted..."_ but this will insert the 2 rows even if they exists (only if there is a PK) – Ilyes Sep 18 '19 at 19:24

Jason Goemaat · Answer 2 · 2019-09-18T19:09:48.817

You can create a pseudo-table using values like so:

select v.Id, v.Name from (values (1, 'Jason'), (2, 'Tony'), (3, 'Michelle')) v(Id, Name)

v is any alias you want to give it and you specify the names for the columns in parentheses. You can combine that with the MERGE statement to only insert those rows if they don't exist.

WITH SOURCE_CTE AS (
  select ChangeReason, IsActive, CreatedByUser, CreatedOn, LastUpdatedByUser, LastUpdatedOn
  from  (values ('Other',        'true', 'system', getdate(), null, null),
                ('Scope Change', 'true', 'system', getdate(), null, null)
  ) tbl (ChangeReason, IsActive, CreatedByUser, CreatedOn, LastUpdatedByUser, LastUpdatedOn)
)
MERGE into dbo.BudgetAndAuthorizationChangeReasons as t
using SOURCE_CTE as s
on    t.ChangeReason = s.ChangeReason
when not matched by target then
  insert (
    ChangeReason,
    IsActive,
    CreatedByUser,
    CreatedOn,
    LastUpdatedByUser,
    LastUpdatedOn
  )
  values
  (
    s.ChangeReason,
    s.IsActive,
    s.CreatedByUser,
    s.CreatedOn,
    s.LastUpdatedByUser,
    s.LastUpdatedOn
  )

* Edit after stored procedure change *

I'm not sure where your data is coming from and how you're passing it. First it seemed like it was just in a sql script. I find things like above handy for config tables, putting a script in the database project post-deploy to make sure values exist.

If using stored procedures, why not just call the stored procedure for each value?

exec SafeInsert_BudgetAndAuthorizationChangeReasons @Reason='Other';
exec SafeInsert_BudgetAndAuthorizationChangeReasons @Reason='Scope Change';

You could create a UDTT and load that with values and pass it to your stored proc:

create type StringList as table (
  value varchar(256)
);

Create Procedure SafeInsert_BudgetAndAuthorizationChangeReasons @Reasons StringList readonly
...

Himanshu Ahuja · Answer 3 · 2019-09-18T18:57:19.260

    INSERT INTO [dbo]. 
 [BudgetAndAuthorizationChangeReasons]
        ([ChangeReason]
        ,[IsActive]
        ,[CreatedByUser]
        ,[CreatedOn]
        ,[LastUpdatedByUser]
        ,[LastUpdatedOn])

        (select 'Other','Scope Change',   
        ,'true'
        ,'system'
        ,GETDATE()
        ,null
        ,null) where NOT EXISTS (SELECT 
           [ChangeReason] FROM 
   BudgetAndAuthorizationChangeReasons WHERE [ChangeReason] IN ('Other','Scope Change'))

or you can have a trigger before insert

    Create Trigger name before insert
        On 
   BudgetAndAuthorizationChangeReasons
      For each row 
      As
      IIF(SELECT COUNT(*) FROM (SELECT 
               [ChangeReason] FROM 

   BudgetAndAuthorizationChangeReasons 
        WHERE [ChangeReason] IN 
      ('Other','Scope Change'))) >0)
      THEN 
      INSERT...... 
      END IF
      END

or you can use a procedure

    checkData(ChangeReason IN 
   BudgetAndAuthorizationChangeReasons.
ChangeReason%TYPE) 
      AS
                IIF(SELECT COUNT(*) FROM 
           (SELECT 
                   [ChangeReason] FROM 


   BudgetAndAuthorizationChangeReasons 
            WHERE [ChangeReason] IN 
          ('Other','Scope Change'))) >0)
          THEN 
          INSERT CHANGEREASON...... 
          END IF 
      END

When I try to run that in MS SQL Studio, I get 'incorrect syntax error' — James L., Sep 18 '19 at 18:43
keep in mind the overhead of trigger. It will fire 5 times if you have 5 columns your checking for OR 20 times if you have 20 columns. Something to consider for your design. — junketsu, Sep 18 '19 at 18:47
I guess i should have used statement level trigger ill update because it checks for whole data if that exists or not — Himanshu Ahuja, Sep 18 '19 at 18:50

score 0 · Answer 4 · answered Sep 19 '19 at 04:40

0

INSERT INTO [dbo].[BudgetAndAuthorizationChangeReasons] ([ChangeReason]
    ,[IsActive]
    ,[CreatedByUser]
    ,[CreatedOn]
    ,[LastUpdatedByUser]
    ,[LastUpdatedOn])
SELECT [ChangeReason],'true','system',getDate(),null,null
FROM BudgetAndAuthorizationChangeReasons
WHERE [ChangeReason]in ('Other','Scope Change')

[dbo].[BudgetAndAuthorizationChangeReasons] table should exist.

answered Sep 19 '19 at 04:40

D.Fulara

1

Thanks! Can you write that to call my stored procedure and I'll accept? – James L. Sep 20 '19 at 22:27

score -1 · Answer 5 · answered Sep 18 '19 at 18:59

You struggle with the code because you struggle to define your goal. And that bleeds into those trying to help. Given a set of reasons, you simply want to all those that do not already exist in the table.

-- declare variables for demo
declare @rsn table (ChangeReason varchar(20) not null primary key, 
IsActive varchar(5) not null, CreatedOn datetime not null);

-- add some "existing" data to table
insert @rsn (ChangeReason, IsActive, CreatedOn) 
values ('Other', 'true', '20190801 13:01:01');

-- demonstrate the use of a table value constructor
with cte as (select * from (values ('Other'), ('Scope'), ('Change')) as x(rsn))
select * from cte;

-- use a transaction for testing just in case you actually use a real table
-- and not a table variable 
begin tran;

merge into @rsn as target
using (values ('Other'), ('Scope'), ('Change')) as source(rsn)
on target.ChangeReason = source.rsn
when not matched by target then
insert (ChangeReason, IsActive, CreatedOn) values (rsn, 'true', getdate())
;

-- verify logic worked correctly
select * from @rsn;

rollback tran;

Here is fiddle to demonstrate. Notice that this makes use of table value constructor which is a very handy thing to know.

How to insert multiple rows into SQL using an Array?

5 Answers5