Marking rows in an SQL Server table as duplicates

Опубликовано: 23 Май 2024
на канале: SQL Server 101

416

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: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
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: http://idodata.com/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): http://idodata.com/database-fundament...
SQL Server Essential in an Hour: http://idodata.com/sql-server-essenti...
70-462 SQL Server Database Administration (DBA): http://idodata.com/sql-server-databas...
DP-300: Administering Relational Databases: http://idodata.com/dp-300-administeri...
Microsoft SQL Server Reporting Services (SSRS): http://idodata.com/microsoft-sql-serv...
SQL Server Integration Services (SSIS): http://idodata.com/sql-server-integra...
SQL Server Analysis Services (SSAS): http://idodata.com/sql-server-ssas-mu...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
1Z0-071 Oracle SQL Developer – certified associate: http://idodata.com/iz0-071-oracle-sql...
SQL for Microsoft Access: http://idodata.com/sql-for-microsoft-...
DP-900: Microsoft Azure Data Fundamentals: http://idodata.com/dp-900-microsoft-a...

Смотрите видео Marking rows in an SQL Server table as duplicates онлайн, длительностью часов минут секунд в хорошем качестве, которое загружено на канал SQL Server 101 23 Май 2024. Делитесь ссылкой на видео в социальных сетях, чтобы ваши подписчики и друзья так же посмотрели это видео. Данный видеоклип посмотрели 416 раз и оно понравилось 18 посетителям.

734