What I am trying to do
- select distinct records from a main table
- Put those records into a temp table
- Clear the records from the main table
- then select the distinct data from the temp back into the main table
I am already checking the main tables and populating a temp table (temp 1) with a list of tables that have duplicate data.
Then by looping through each row in that temp 1 table, using a cursor, to see what tables are duplicate via count value of duplicates, I wanted to use the actual table name, which is loaded into a variable then into temp 1 table, to then select the distinct records from this table into another temp table 'table 2' lets say.
Then I want to clear the main table and reload the distinct data from temp 2 back into the main table.
This is the statement before cursor that loads the temp 1 table with the table name that has duplicates
INSERT INTO #TEMP_TBL_DUP_CNT select cast(@rec_cnt as varchar(10)) reccnt, cast(@tbl_name as varchar(200)) tblnm
So this is my cursor below and i have numbered my ideas below 1-4 as related to bullet list above. The syntax is the bit I am struggling with and would like help as to how to create it.
DECLARE REC_CURSOR CURSOR FOR -- Set select stamtent for cursor select reccnt, tblnm from #TEMP_TBL_DUP_CNT OPEN REC_CURSOR FETCH NEXT FROM REC_CURSOR INTO @rec_cnt, @tbl_name WHILE @@FETCH_STATUS = 0 BEGIN -- Check if record count is 0 -- if is larger than 0 then select distinct records from tbl name into -- temp table if @rec_cnt > 0 -- Need to load data into new temp table using dynamic SQL and @tbl_name -- Ideally wanted to create a temp Table with dynamic name like -- '#TEMP_' + @tbl_name giving #temp_Table_Name_1, #temp_Table_Name_2 etc. -- 1) Starting with something like : -- SET @tbl_temp = '#TEMP_' + @tbl_name 2) Then select distinct * into temp like this: -- SELECT 'distinct * INTO ' + @tbl_temp + ' FROM ' + @tbl_name 3) Then clear main table -- TRUNCATE TABLE + '@tbl_name' 4) Then select from temp back into main table -- SELECT '* INTO ' + @tbl_name + ' FROM ' + @tbl_temp FETCH NEXT FROM REC_CURSOR INTO @rec_cnt, @tbl_name END CLOSE REC_CURSOR DEALLOCATE REC_CURSOR
It doesn't have to be a cursor but this is what I decided on to build. If anyone has any other suggestion then please feel free to suggest.
Thanks for any input.