当前位置: 代码迷 >> Sql Server >> 怎么排除存储过程写入重复记录
  详细解决方案

怎么排除存储过程写入重复记录

热度:8   发布时间:2016-04-25 01:18:56.0
如何排除存储过程写入重复记录?
有个表如下:
SQL code
CREATE TABLE [dbo].[T_FillingRec](    [FillingRec] [uniqueidentifier] NOT NULL,    [Organize] [varchar](16) NULL,    [SerialPort] [int] NULL,    [Sequence] [bigint] NULL,    [BUTime] [datetime] NULL,    [BUUserName] [varchar](32) NULL, CONSTRAINT [T_Gas_FillingRec_PK] PRIMARY KEY CLUSTERED (    [FillingRec] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]

  其主键是[FillingRec] [uniqueidentifier] ,每次都是New一个GUID出来。
  这个表是存储多个下位机传过来的数据的一个表,每个下位机占用一个串口SerialPort,每条记录的Sequence加一,但是当上位机没有给下位机应答的时候,下位机会重复传输这条记录,也就是Sequence有可能传一致的,因此这个表实际上必须根据SerialPort和Sequence作为主键是最合适的,但是表结构就是这样了,不好修改。
  上位机使用了完成端口的方式处理下位机传输过来的数据,因此在同一个Sequence的记录中,可能会有不同的线程在处理数据写入的工作,可能是由于不同记录中的事务问题,导致极其偶尔的同一个串口有重复Sequence的记录存在。
  请问如何解决这个问题?
  现在的存储过程如下:
SQL code
Create PROCEDURE [dbo].[pro_T_FillingRec1_Insert]    @FillingRec uniqueidentifier,    @Organize varchar(16),    @SerialPort int,    @Sequence bigint,    @BUTime datetime,    @BUUserName varchar(32)ASSET NOCOUNT ONIF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)BEGIN    UPDATE [dbo].[T_FillingRec1] SET        [Organize] = @Organize,        [BUTime] = @BUTime,        [BUUserName] = @BUUserName     WHERE        SerialPort = @SerialPort And Sequence = @SequenceENDELSEBEGIN    Set @FillingRec = NEWID()    INSERT INTO [dbo].[T_FillingRec1]        ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])        Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserNameEND


请问这个存储过程有什么地方可以改进的?






------解决方案--------------------
uniqueidentifier的话无论多接近的描述,哪怕真的达到同一刻也不会重复的,你可以在表该字段直接指定默认值newid()函数,就会自动生成拉。
我的文章,希望可以帮到你:http://blog.csdn.net/dba_huangzj/article/details/7699166
------解决方案--------------------
SQL code
--如果你的系统是2008的,那么使用merge将是完美方案IF EXISTS(SELECT * FROM [dbo].[T_FillingRec1] WHERE SerialPort = @SerialPort And Sequence = @Sequence)BEGIN    UPDATE [dbo].[T_FillingRec1] SET        [Organize] = @Organize,        [BUTime] = @BUTime,        [BUUserName] = @BUUserName     WHERE        SerialPort = @SerialPort And Sequence = @SequenceENDELSEBEGIN    Set @FillingRec = NEWID()    INSERT INTO [dbo].[T_FillingRec1]        ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])        Select @FillingRec,@Organize,@SerialPort,@Sequence,@BUTime,@BUUserNameENDMERGE dbo.[T_FillingRec1] AS TargetUSING (SELECT SerialPort, Sequence,[BUTime],[BUUserName],[Organize] from xxx /*所有的新值*/) AS SourceON (Target.SerialPort = Source.SerialPort AND Target.Sequence = Source.Sequence)WHEN MATCHED THEN    UPDATE SET Target.[BUTime] = Source.[BUTime], Target.[BUUserName] = Source.[BUUserName], Target.[Organize] = Source.[Organize],WHEN NOT MATCHED BY TARGET THEN    INSERT ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])    VALUES (Source.[FillingRec], Source.[Organize], Source.[SerialPort], Source.[Sequence], Source.[BUTime], Source.[BUUserName]);
------解决方案--------------------
SQL code
--只是下面这段MERGE dbo.[T_FillingRec1] AS TargetUSING (SELECT SerialPort, Sequence,[BUTime],[BUUserName],[Organize] from xxx /*所有的新值*/) AS SourceON (Target.SerialPort = Source.SerialPort AND Target.Sequence = Source.Sequence)WHEN MATCHED THEN    UPDATE SET Target.[BUTime] = Source.[BUTime], Target.[BUUserName] = Source.[BUUserName], Target.[Organize] = Source.[Organize],WHEN NOT MATCHED BY TARGET THEN    INSERT ([FillingRec],[Organize],[SerialPort],[Sequence],[BUTime],[BUUserName])    VALUES (Source.[FillingRec], Source.[Organize], Source.[SerialPort], Source.[Sequence], Source.[BUTime], Source.[BUUserName]);