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