代码之家 › 专栏 › 技术社区 › James L.

如何使用数组将多行插入SQL?

sql-server sql

James L. · 技术社区 · 5 年前

我想在数据库中插入多行(如果它们不存在),每次插入时交换一个列值。以下是目标的一些伪代码:

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

换句话说,如果一行 ChangeReason='Other' 已经存在,什么也不会发生。如果它不存在,则将与上述所有其他值一起插入(IsActive=true等)。这将对数组中的每个元素重复:('Other','Scope Change')

编辑:

我已经编写了一个存储过程来处理我的脏活。可以为数组中的每个元素自动调用它吗?或者我需要X个不同的exec语句吗?

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')

1 回复 | 直到 5 年前

Ilyes 5 年前

SQL不理解数组,但可以像下面这样列出多个插入值。

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

Jason Goemaat 5 年前

可以使用如下值创建伪表:

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

v 是您要为其指定的任何别名,并为括号中的列指定名称。您可以将其与 MERGE statement

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
  )

*存储过程更改后编辑*

我不确定你的数据来自哪里,以及你是如何传递的。首先,它似乎只是在一个sql脚本中。我发现上面这样的事情对于配置表很方便,在部署后将脚本放入数据库项目中以确保值存在。

如果使用存储过程,为什么不为每个值调用存储过程?

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

您可以创建一个UDTT并用值加载它,然后将其传递给存储过程:

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

Create Procedure SafeInsert_BudgetAndAuthorizationChangeReasons @Reasons StringList readonly
...

Himanshu 5 年前

    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'))

或者你可以在插入前触发

    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

或者你可以使用一个程序

    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

D.Fulara 5 年前

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]表应该存在。

-1

SMor 5 年前

你在与代码作斗争,因为你在努力定义你的目标。这让那些试图帮助的人感到痛苦。考虑到一系列原因,您只需要删除表中不存在的所有内容。

-- 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;

fiddle 演示。注意,这利用了 table value constructor 这是一件非常方便的事情。