Forum Discussion

russellhq's avatar
russellhq
Regular Visitor
2 years ago
Solved

Deleting Duplicate Rows in a Data Warehouse Table?

I've created a data warehouse which has a dataflow that connects to an excel file. Each week the excel file is updated but some of the old data is still in it, so when the dataflow runs and appends t...
Need Help
  • lbendlin's avatar
    lbendlin
    2 years ago

    Nothing to be proud of but if it works and doesn't break any GUIDs then so be it.

     

    Maybe add an index column to your data to avoid this in the future.

russellhq's avatar
russellhq
Regular Visitor
2 years ago

OK, I've managed to come up with a solution for this, I don't know if it's a very good one, but it seems to work for what I want it to do.

 

The solution was to write a stored procedure that creates a Temp table by selecting the distinct rows in the original table. Then it drops the original table, creates a new table using the original tables name and selects all records from the temp table, then drops the temp table.

 

The procedure looks like this:

 

SET ANSI_NULLS ON;

SET QUOTED_IDENTIFIER ON;

CREATE TABLE [Excel Warehouse].[dbo].[TempDailyChecks] AS
SELECT DISTINCT *
FROM [DailyChecks];

DROP TABLE [DailyChecks];

CREATE TABLE [Excel Warehouse].[dbo].[DailyChecks] AS
SELECT *
FROM [TempDailyChecks];

DROP TABLE [TempDailyChecks];

 

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Nothing to be proud of but if it works and doesn't break any GUIDs then so be it.

     

    Maybe add an index column to your data to avoid this in the future.

    • russellhq's avatar
      russellhq
      Regular Visitor
      2 years ago

      Thanks lbendlin 

      My data comes from an excel spreadsheet that's exported from an online service and it has no index column. So I'm not sure how best to go about creating one.

       

      For example, if I create an index column in my dataflow when I import it into my table in the Data Warehouse, when I get a new excel file and it contains some rows that are already in the data warehouse, I'm not sure where I'd go from here to match the rows in the data warehouse to the rows in the excel workbook as the index would likely be different.

       

      Using DISTINCT seems to be the only way I could think of. But maybe this is my lack of understanding as I'm pretty new to SQL and databases in general.

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago
        My data comes from an excel spreadsheet that's exported from an online service and it has no index column

        yeah, that's unfortunate.  If your solution works reliably in a reasonable amount of time then leave it as is.