存储过程/ SQL脚本，允许我向表添加记录 [英] Stored Procedure/SQL script that allows me to add a record to a table

问题描述

我有2个表，一个表包含所有电子邮件数据，另一个表包含所有特定的成员电子邮件数据，如果已读取电子邮件，则其中一个会创建一行。
成员读取电子邮件后，将其添加到Member_email_read表（该表根据所有已读取的电子邮件创建和填充）。

I have 2 tables, one with all the email data, and another with all the specific member email data that one creates a row if an email has been read. Once an email has been read by a member its added to the Member_email_read table (which is created and populated based on all read emails).

我正在尝试设置（批量）所有要读取的消息（这将填充Member_email_read表），但是虽然我可以一次将它们添加一次
（请参见下面的存储过程），但无法批量添加它们。

I am trying to set (on mass) all of the messages to read (this would populate the Member_email_read table) but whilst I can add them one at a time (see the stored procedue below), I am unable to add them on mass.

这两个表是Email，它保存进入系统的每封电子邮件的记录。另一个表是该成员已阅读的所有电子邮件的表。每次读取电子邮件
时，都会将一条记录添加到Member_email_read表中。它们与message_id关联（并且两者都应使用相同的user_id）。这两个表如下-

The two tables are Email, which holds a record for every email into the system. The other table is a table of all email that the member has read. Each time an email is read a record is added to the Member_email_read table. They are assiocited on the message_id (and both should use the same user_id). The two tables are as follows -

SELECT [member_email_id]
  ,[member_email_FK_message_id]
  ,[member_email_FK_user_id]
  ,[member_email_status]
  ,[member_email_read_datetime]
  ,[member_email_delete_datetime]
FROM [MemberData].[dbo].[Member_email_read]



SELECT[message_id]
  ,[email_catlogue_num]
  ,[email_FK_user_id]
  ,[Email_time]
  ,[email_content]
  ,[Email_created_date]
FROM [MemberData].[dbo].[Email]

设置所有要读取的消息（对于某个用户），我要做的就是从该用户表中删除该用户的每条记录，可以通过以下操作完成：

To set all the messages (for a certain user) to unread all I would have to do is delete every record from that table for that user, which can be done with the following:

DELETE FROM [MemberData].[dbo].[Member_email_read]
WHERE [member_email_FK_message_id_FK_user_id] ='2';

我基本上是在寻找与此删除操作相反的情况。

I am basically looking for the reverse of this delete.

我创建了一个存储过程，该过程允许设置要读取的特定电子邮件，但是此存储过程（执行时）要求成员输入
email_id，message_id，user_id，状态，读取日期时间& delete_datetime。

I have created a Stored procedure that allows for the setting of ONE specific email to be set to read, however this stored procedure (when executed) requires the member to enter a email_id, message_id, user_id, status, read_datetime & delete_datetime.

CREATE PROCEDURE [dbo].[set_member_email_to_read]

@member_email_id int,
@member_email_FK_message_id int,
@member_email_FK_user_id int
@member_email_status varchar(1),
@member_email_read_datetime dateTime,
@member_email_delete_datetime dateTime

as
if not exists (Select * from [dbo].[Member_email_read] where [member_email_FK_message_id] = @member_email_FK_message_id) begin
insert into [dbo].[Member_email_read]
    (
       [member_email_FK_message_id]
      ,[member_email_FK_user_id]
      ,[member_email_status]
      ,[member_email_read_datetime]
      ,[member_email_delete_datetime]
  )
  values
  (
        @member_email_FK_message_id,
        @member_email_FK_user_id
        @member_email_status,
        @member_email_read_datetime,
        @member_email_delete_datetime
  )


SELECT Convert(int,SCOPE_IDENTITY()) As InsertedID

end else begin
    update [dbo].[Member_email_read] set
   [member_email_FK_message_id]         =   @member_email_FK_message_id
  ,[member_email_FK_user_id]            =   @member_email_FK_user_id
  ,[member_email_status]                =   @member_email_status
  ,[member_email_read_datetime]         =   @member_email_read_datetime
  ,[member_email_delete_datetime]       =   @member_email_delete_datetime

where [member_email_FK_user_id] = @member_email_FK_user_id
if (@@ERROR = 0) begin
    SELECT Convert(int,@member_email_FK_user_id) As InsertedID
end
end
GO

我希望创建一个存储过程（或通用SQL脚本），该存储过程将允许我输入
a user_id，然后允许所有电子邮件发送用户从未读更改为已读（填充Member_email_read表）。

I was hoping to create a stored procedure (or general SQL script) that would allow me to enter in a user_id and then allow for all emails for that user to change from unread to read (populate the Member_email_read table).

推荐答案

您可以尝试使用 MERGE 执行将 Email 表中的批量插入 / 更新 Member_email_read 表：

You can try using MERGE to perform a bulk insert/update from your Email table into your Member_email_read table:

MERGE [MemberData].[dbo].[Member_email_read] AS tgt  
USING (
    SELECT message_id, user_id, 'R', null, CURRENT_TIMESTAMP
    FROM [MemberData].[dbo].[Email] 
    WHERE user_id = @UserId
) AS src (MessageId, UserId, Status, ReadDate, DeleteDate) 
ON (
    tgt.member_email_FK_message_id = src.message_id
    AND tgt.member_email_FK_user_id = src.user_id
)  
WHEN MATCHED THEN   
    UPDATE SET tgt.member_email_status = src.Status,
               tgt.member_email_read_datetime = src.ReadDate,
               tgt.member_email_delete_datetime = src.DeleteDate
WHEN NOT MATCHED THEN  
    INSERT (member_email_FK_message_id, member_email_FK_user_id, 
            member_email_status, member_email_read_datetime, member_email_delete_datetime)  
    VALUES (src.MessageId, src.UserId, src.Status, src.ReadDate, src.DeleteDate)
;

这应该可行，尽管正如我在上面的评论中提到的那样，您应该重新考虑表的设计，并简单地添加 read_datetime 和 delete_datetime （如果 status 列，则添加（也确实需要）到 Email 表中，而不是拥有一个单独的表来简单地保存标识删除状态的记录。

This should work, though as I mentioned in my comment above, you should rethink your table design and simply add a read_datetime and delete_datetime (possibly a status column if you really need that as well) to your Email table, rather than having a whole separate table simply to hold records identifying a delete status.

这篇关于存储过程/ SQL脚本，允许我向表添加记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！