May 14, 2007 at 8:03 pm
Hey,
This is my first post, so here goes...
I have an initial table were I check for duplicates, If any dups are found, I want to append these records to an already existing table and insert along with each record a date stamp. I know how to do this in oracle. But I am not quite sure how to best approach this in ms sql. Could this be done with an insert. Any recommendations? Or anything in particular to keep in mind?
Any responses are appreciated.
Thanks,
Jill
May 14, 2007 at 9:17 pm
It would be done pretty much the same way in MS-SQL Server as you did in Oracle... unless you used a cursor which is not the most effective way to do it in MS-SQL Server...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2007 at 8:46 am
Thanks,
It was easier than I thought.
This is sort of what I did....
INSERT INTO DuplicateTable (id, lastname, firstname, Mypk, dateStamp)
SELECTid, lastname, firstname, Mypk, getDate()
FROMINPUT_RECORDS
WHEREMypk is not null
GROUP BY id, lastname, firstname, Mypk
HAVING COUNT(*) >1
Now all I have to do is figure out how to delete the dups found in the INPUT_RECORDS.
Thanks,
~J
May 15, 2007 at 1:29 pm
what are the primary keys in you Input table?
May 15, 2007 at 2:28 pm
Mypk is supposed to be the primary key but this is not always the case.
This is an integration of multiple sources and ID is the true primary key.
But can not be used through the entire process.
Any special reason you asked? 🙂
May 18, 2007 at 11:10 am
Hi Jay. This is my first posting/ reply, so please be gentle if this doesn't work for you...
Here is what I do to eliminate duplicates. If you can use just 1 column, make the temp table with just the values of that one column.
That will make it easier and faster.
Step 1) Create a list of values to be evaluated (temp table) - * I use a Memory table because my numbers are small, but if
you have thousands of rows, use a standard temp table.
Step 2) Decide if you are going to "mark" the data you want to keep or delete all and insert the one you want to keep. * - This
will help you determine what your temp table will look like. If it is inserting, your temp table will have all columns.
Step 3) Run the evaluation process which will cycle through the list and remove duplicates. Then it will remove the entry
from the list (completed row) and continue until there are no more entries in the list (temp table).
Like this...
Declare @DupeListTable Table (id, lastname, firstname, Mypk, dateStamp)
INSERT INTO @DupeListTable (id, lastname, firstname, Mypk, dateStamp)
SELECT id, lastname, firstname, Mypk, getDate()
FROM INPUT_RECORDS
WHERE Mypk is not null
GROUP BY id, lastname, firstname, Mypk
HAVING COUNT(*) >1
Declare@var_ID as int,
@var_LASTNAME as varchar(100),
@var_FIRSTNAME as varchar(100),
@var_MYPK as varchar(100)
WHILE (Select count(*) from @DupeListTable) >0
BEGIN
Set @var_ID = (Select TOP 1 id from @DupeListTable order by id ASC)
Set @var_LASTNAME = (Select TOP 1 lastname from @DupeListTable where @var_ID = id)
Set @var_FIRSTNAME = (Select TOP 1 firstname from @DupeListTable where @var_ID = id)
Set @var_MYPK = (Select TOP 1 Mypk from @DupeListTable where @var_ID = id)
/* To Delete and Replace... */
DELETE FROM INPUT_RECORDS
WHERE id = @var_ID
AND lastname = @var_LASTNAME
AND firstname = @var_FIRSTNAME
AND Mypk = @var_MYPK
INSERT INTO INPUT_RECORDS (id, lastname, firstname, Mypk, )
SELECT @var_ID, @var_LASTNAME, @var_FIRSTNAME, @var_MYPK, getdate()
DELETE FROM @DupeListTable
WHERE id = @var_ID
AND lastname = @var_LASTNAME
AND firstname = @var_FIRSTNAME
AND Mypk = @var_MYPK
END
Let me know how it works out.
Later.
J.T.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply