SQL Server stored procedure deletes a table (except for the ID) of a fully duplicated data record

1 CREATE PROCEDUREP_deletesamedata2 (3     @table nvarchar( -),--Table name4      @columns nvarchar( $)--column name, format such as: Id,name,age5  )6   as7  BEGIN8      BEGINTRY9           BEGIN TRANSACTIONTen              DECLARE @SQL NVARCHAR( +),@tempTable NVARCHAR( -); One              SET @SQL = "'; A              IF object_id('[#tempTable]') is  not NULL -              BEGIN -                  --PRINT ' aaa ' the                  SET @SQL = @SQL + 'DROP TABLE [dbo]. [#tempTable]'; -              END -              SET @tempTable = '[#tempTable]'; -              SET @SQL = @SQL + 'SELECT * into'+@tempTable+' from'+@table+'WHERE 1<>1;'; +  -              --Create a temporary table index that ignores repeating column data +              SET @SQL = @SQL + 'CREATE UNIQUE INDEX tempindex on'+@tempTable+'(' + @columns + ') with Ignore_dup_key;';  A                         at              --Data from the source table is inserted into the staging table, and the data for the repeating column is ignored, so the data in the staging table is already the deduplication data. -              SET @SQL = @SQL + 'INSERT into'+@tempTable+'('+@columns+') SELECT' + @columns + ' from' + @table + ';';  -                    -              --emptying source table data -              SET @SQL = @SQL + 'TRUNCATE TABLE' + @table + ';'; -              --Copy the data from the staging table to the source table again in              SET @SQL = @SQL + 'INSERT into'+@table+'('+@columns+') SELECT'+@columns+' from'+@tempTable+';'; -              SET @SQL = @SQL + 'DROP TABLE'+@tempTable + ';'; to  　　　　　　　　  --execute all of the above statements dynamically +              EXEC(@SQL); -   the          COMMIT TRANSACTION *      ENDTRY $      BEGINCATCHPanax Notoginseng          ROLLBACK TRANSACTION -          DECLARE @msg nvarchar(4000) the          SET @msg=error_message (); +          RAISERROR(@msg, One,1) A          RETURN 1; the      ENDCATCH +  END

View Code

Execute the sample code as follows:

1 EXEC @table = ' T_teacher ' @columns = ' Name,age '

When using the stored procedures described above, it is important to note that:

1. If the primary key ID of the deduplication table is autogrow or the GUID is automatically generated, that is, the table cannot be duplicated in the column, then the ID column cannot be passed into the @columns, such as: @columns = ' Stuname,stuage,stusex ',

If you want to satisfy all the columns in the table that require no repetition: @columns = ' stuid,stuname,stuage,stusex ' in this form.

2. Some classmates or colleagues want to ask why all the statements are stitched together in the last execution? Because the interim table is used in the middle, and the scope of the temporary table (scope is a term in computer programming, if you are fortunate to see this article, I believe everyone knows ~) can only be in the same dynamic statement, so in the final joint temporary table together dynamic execution. One way to do this is to use a global temporary table, but it is not recommended to consume resources.

SQL Server stored procedure deletes a table (except for the ID) of a fully duplicated data record