Marking rows in an SQL Server table as duplicates

Have you got duplicate rows in your data? Here's how you are can find them and mark them as duplicate. My SQL Server Udemy courses are: 70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/querying-microsoft-sql-server 98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/database-fundamentals 70-462 SQL Server Database Administration (DBA): rebrand.ly/sql-server-dba Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql-server-ssrs SQL Server Integration Services (SSIS): rebrand.ly/sql-server-ssis SQL Server Analysis Services (SSAS): rebrand.ly/sql-server-ssas-mdx Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpivot-ssas-tabular-dax ---- In this video, we will create a new table with two columns - "name" and ID. We will then find where a "name" has been used for more than once, and then mark them as duplicates. You can then review them and manipulate them as you want. --- Here is the code for this video: SELECT * FROM sys.columns DROP TABLE IF EXISTS tblColumns GO SELECT [name], ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID INTO tblColumns FROM sys.columns WITH myTable AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY ID) - 1 AS RowNumbers FROM tblColumns ) SELECT * FROM myTable WHERE RowNumbers != 0 SELECT *, ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY ID) - 1 AS RowNumbers FROM tblColumns ALTER TABLE tblColumns ADD IsDuplicate INT UPDATE tblColumns SET IsDuplicate = ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY ID) - 1 WITH myTable AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY ID) - 1 AS RowNumbers FROM tblColumns ) UPDATE myTable SET IsDuplicate = RowNumbers SELECT * FROM tblColumns ---- Links to my website are: 70-461, 70-761 Querying Microsoft SQL Server with T-SQL: idodata.com/querying-microsoft-sql-server-with-t-s… 98-364: Database Fundamentals (Microsoft SQL Server): idodata.com/database-fundamentals-it-specialist-it… SQL Server Essential in an Hour: idodata.com/sql-server-essentials-in-an-hour-the-s… 70-462 SQL Server Database Administration (DBA): idodata.com/sql-server-database-administration-dba… DP-300: Administering Relational Databases: idodata.com/dp-300-administering-relational-databa… Microsoft SQL Server Reporting Services (SSRS): idodata.com/microsoft-sql-server-reporting-service… SQL Server Integration Services (SSIS): idodata.com/sql-server-integration-services-ssis-a… SQL Server Analysis Services (SSAS): idodata.com/sql-server-ssas-multidimensional-mdx-a… Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/microsoft-powerpivot-ssas-tabular-dax 1Z0-071 Oracle SQL Developer – certified associate: idodata.com/iz0-071-oracle-sql-developer-certified… SQL for Microsoft Access: idodata.com/sql-for-microsoft-access/ DP-900: Microsoft Azure Data Fundamentals: idodata.com/dp-900-microsoft-azure-data-fundamenta…

• #science_technology
• #sql_server
• #sql
• #microsoft_sql_server
• #sql_server_learn
• #sql_server_videos
• #t_sql
• #tsql
• #char
• #nchar
• #sql_server_function
• #distinct
• #sql_distinct
• #distinct_sql
• #group_by_sql
• #sql_group_by
• #union_sql
• #sql_union
• #sql_duplicate
• #remove_duplicate
• #duplicate_sql