如何通过指定表名来创建存储过程以从sql server db中删除冗余行？

+-----+------------+-----------+-------+--------------+
| cid |  LastName  | FirstName | City  | customerType |
+-----+------------+-----------+-------+--------------+
|   1 | joshi      | ram       | ktm   |            1 |
|   2 | sundar     | shayam    | ktm   |            0 |
|   3 | Probert    | Asia      | ltp   |            1 |
|   4 | Pepper     | Carmela   | bkt   |            0 |
|   5 | Grantham   | Odell     | klnk  |            1 |
|   6 | Granillo   | Agustina  | patan |            1 |
|   7 | Moczygemba | Phyllis   | bkt   |            2 |
|   8 | Moczygemba | Phyllis   | bkt   |            2 |
|   9 | sundar     | shayam    | ktm   |            0 |
|  10 | bilas      | gita      | ktm   |            5 |
|  11 | sas        | ram       | ktm   |            2 |
+-----+------------+-----------+-------+--------------+

I want all non redundant data. But just by passing table name for the stored procedure so that I can reuse the sp for any other tables.

我想要所有非冗余数据。但只需传递存储过程的表名,以便我可以将sp重用于任何其他表。

ALTER Procedure [dbo].[deleteDupTables] @TABLENAME varchar(100)
AS
Begin


DECLARE @order varchar(1000)
DECLARE @columns varchar(1000)

SELECT @order = QUOTENAME(column_name), @columns = ISNULL(@columns + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLENAME AND COLUMN_NAME not in (Select 
kcu.COLUMN_NAME
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
where
tc.CONSTRAINT_TYPE = 'PRIMARY KEY' and
tc.TABLE_SCHEMA = 'dbo' and
tc.TABLE_NAME = @TABLENAME)
ORDER BY ORDINAL_POSITION

Declare @Delete varchar(2000)
Set @Delete = 'WITH q AS (Select ' + @columns + ', ROW_NUMBER() OVER (PARTITION BY  ' + @columns + ' ORDER BY ' + @order + ' ) AS rn FROM ' + @TABLENAME + ') DELETE FROM q WHERE rn > 1 ;';
exec (@delete)



End

Tried this... But I am not satisfied with the process. This is fairly easy problem. It should have easy solution.

尝试过这个......但我对这个过程并不满意。这是一个相当容易的问题。它应该很容易解决。

1 个解决方案

#1

Try This one

试试这个

WHILE 1 = 1
    BEGIN
        DELETE FROM dbName..tblName
        WHERE DateLogged IN (SELECT cid
                            FROM  dbName..tblName WITH(NOLOCK)
                            GROUP BY cid
                            HAVING  COUNT(*) > 1
                            ORDER BY cid)
        IF @@Rowcount = 0
        BREAK ;
    END