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/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
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/que...
98-364: Database Fundamentals (Microsoft SQL Server): idodata.com/dat...
SQL Server Essential in an Hour: idodata.com/sql...
70-462 SQL Server Database Administration (DBA): idodata.com/sql...
DP-300: Administering Relational Databases: idodata.com/dp-...
Microsoft SQL Server Reporting Services (SSRS): idodata.com/mic...
SQL Server Integration Services (SSIS): idodata.com/sql...
SQL Server Analysis Services (SSAS): idodata.com/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
1Z0-071 Oracle SQL Developer - certified associate: idodata.com/iz0...
SQL for Microsoft Access: idodata.com/sql...
DP-900: Microsoft Azure Data Fundamentals: idodata.com/dp-...
5 окт 2024
