sqlserver,将视图或表的记录不重复的插入到另一个表
最近在做个自动更新数据的小程序,每天有几个表的txt记录集要更新到数据库,并且txt记录集中的数据列并不全用,只使用一部分。
在做完更新程序后,发现还少了一个功能:自动提示重要数据的功能,在编写时发现需要一个表记录是否关联表记录是否已提示,这牵涉一个问题,插入提示记录的表数据会重复导致重复提示,没有必要的短信会发送,当然可以通过以下步骤判断记录是否是重复:
- 将关键字段生成唯一索引
- 从视图或表中读取记录插入到提示表
这样逻辑比较简单,但是效率有点低了,要与sqlserve多次通讯,能不能在数据库端就完成这些操作呢,反正取记录、更新到表都是固定的,在网上找了一下倒是有不少,但没有完整的实现,经过一上午的努力,终于解决了问题,所以作文以记之。
由于工作关系,不便使用原数据,生成了两个模拟的表,结构如下
CREATE TABLE [dbo].[t1]( [c1] [nchar](10) NOT NULL, [c2] [nchar](20) NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[t2]( [Id] [int] IDENTITY(1,1) NOT NULL, [c1] [nchar](10) NOT NULL, [c2] [nchar](20) NOT NULL, [IsHandled] [bit] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[t2] ADD CONSTRAINT [DF_t2_IsHandled] DEFAULT ((0)) FOR [IsHandled] GO --生成数据 INSERT INTO [test].[dbo].[t1]([c1],[c2]) VALUES ('a1','a2') INSERT INTO [test].[dbo].[t1]([c1],[c3]) VALUES ('a1','a3') INSERT INTO [test].[dbo].[t1]([c1],[c4]) VALUES ('a1','a4') INSERT INTO [test].[dbo].[t1]([c1],[c5]) VALUES ('a1','a5') INSERT INTO [test].[dbo].[t1]([c1],[c6]) VALUES ('a1','a6') INSERT INTO [test].[dbo].[t1]([c1],[c7]) VALUES ('a1','a7') INSERT INTO [test].[dbo].[t1]([c1],[c8]) VALUES ('a1','a8') INSERT INTO [test].[dbo].[t1]([c1],[c9]) VALUES ('a1','a9') INSERT INTO [test].[dbo].[t1]([c1],[c10]) VALUES ('a1','a2') INSERT INTO [test].[dbo].[t1]([c1],[c11]) VALUES ('a1','a11')
思路:阻止重复数据插入不外两个办法,一是利用唯一索引,二是利用事务,插入后判断同样的记录是否有两条,是的话,回滚事务。具体实现如下:
- 读取T1的数据,
- 用游标读取每一条记录,并插入T2
方法一、利用唯一索引
方法二、利用事务,在这里建立一个存储过程方便调用
Create procedure [dbo].[insert_t2] @c1 varchar(10), --定义一个输入参数,就是那个是不是重复的值 @c2 varchar(20) --把每一列弄成变量存入,不知你的表有几字段,这里我就以两个字段为例 as declare @sum int begin tran insert into t2 (c1,c2) values (@c1,@c2) select @sum=count(*) from t2 where (c1=@c1) and (c2=@c2) if(@sum>1) begin raiserror('该记录已经存在',16,8) rollback tran --滚回事务 end else commit tran --提交事务
下面是从T1取数据存入游标的存储过程
Create procedure [dbo].[GetT1] @MyCursor Cursor Varying Output --With Encryption As Set @MyCursor = Cursor For Select C1,C2 From T1 Open @MyCursor
将数据插入T2的存储过程
Create Procedure InsertIntoT2 As Declare @c1 varchar(20) Declare @c2 nvarchar(20) Declare @T1DataCursor Cursor Exec GetT1 @T1DataCursor out Fetch Next From @T1DataCursor InTo @c1,@c2 While(@@Fetch_Status = 0) Begin exec [test].[dbo].[insert_t2] @c1, Fetch Next From @T1DataCursor InTo @c1,@c2 End Close @T1DataCursor Deallocate @T1DataCursor Go
调用方法
结果
可以看到t2只有9行记录