Sqlserver, insert records of views or tables into another table without repeating

Recently, I am working on a small program to automatically update data. Every day, the TXT record set of several tables must be updated to the database. In addition, the data columns in the TXT record set are not all used and only part of the data is used.

After the update program is completed, it is found that there is still one function missing: the function of automatically prompting important data. during compilation, it is found that a table record is required to indicate whether the associated table record has been prompted. This involves a problem, the table data inserted in the prompt record is duplicated, leading to repeated prompts. If no necessary text message is sent, you can determine whether the record is duplicated by performing the following steps:

1. Generate a unique index for a key field
2. Read records from a view or table and insert them to the prompt table.

The logic is simple, but the efficiency is a little low. If you need to communicate with sqlserve for multiple times, can you complete these operations on the Database End? It is fixed to retrieve records and update data to tables, I found a lot on the Internet, but it was not completely realized. After a morning's efforts, I finally solved the problem, so I wrote a note.

 

The original data cannot be used due to the working relationship, and two simulated tables are generated. The structure is as follows:

Create Table [DBO]. [T1] ([C1] [nchar] (10) Not null, [C2] [nchar] (20) not null) on [primary] gocreate 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] goalter table [DBO]. [T2] add constraint [df_t2_ishandled] default (0) for [ishandled] Go -- generate data 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 ')

Idea: There are two methods to prevent duplicate data insertion. One is to use a unique index, and the other is to use transactions to determine whether there are two identical records after insertion. If yes, roll back the transaction. The specific implementation is as follows:

1. Read data of T1,
2. Read each record with a cursor and insert T2

Method 1: Use unique Indexes

 

Method 2: Use transactions to create a stored procedure for convenient calling.

Create procedure [DBO]. [insert_t2] @ C1 varchar (10) -- defines an input parameter, that is, whether it is a repeated value @ C2 varchar (20) -- stores each column as a variable, I don't know how many fields your table has. Here I will take two fields as an example 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 ('this record already exists ',) rollback tran -- roll back transaction end else commit tran -- commit transaction

The following describes how to store data from T1 to the cursor.

Create procedure [dbo].[GetT1]      @MyCursor Cursor Varying Output      --With Encryption      As              Set @MyCursor = Cursor             For                    Select C1,C2 From T1      Open @MyCursor

Stored Procedure for inserting data into 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)      Beginexec [test].[dbo].[insert_t2] @c1,                         Fetch Next From @T1DataCursor             InTo @c1,@c2      End      Close @T1DataCursor      Deallocate @T1DataCursor      Go

Call Method

Declare @ RC int -- todo: Set the parameter value here. Execute @ rc = [test]. [DBO]. insertintot2go

Result

SELECT TOP 1000 [Id]      ,[c1]      ,[c2]      ,[IsHandled]  FROM [test].[dbo].[t2]

We can see that T2. there are only nine rows of records.