Bulk move notes from one customer record to another
-
We currently have some duplicate customer records that I can identify by matching their names, address, and basic contact info. I would like to keep on of the duplicate account active and delete the others.
Since I am only keeping one of the profiles, I would like to move all the notes from the records that are going to be obsolete and move them to the active profile.
Currently, I am doing this via SQL Management Studio one-by-one but it does take a long time. I was wondering if this project could be done by executing a block of SQL query without causing any affect to the other customer profiles.
Thank you,
Fawad Rashidi
www.fawadafr.com -
I would use ROW_NUMBER() OVER(PARTITION BY names, address, [basic contact info] ORDER BY hopefully_existing_rowid) as RowNmbr to identify the separate rows.
The ROW_NUMBER would be used within a CTE in order to be able to use the value in the DELETE statement.
When deleting the rows with RowNmbr >1 I would store the data in an intermediate table using the OUTPUT clause.
Finally, I would add the notes to the remaining rows.
If you'd like to see a coded version please provide table definition and sample data in a ready to use format as described in the first link in my signature.
-
Would this be a good table definition?
SELECT [Counter]
,[NoteCode]
,[EntityCode]
,[ContactCode]
,[ParentType]
,[OwnerCode]
,[Title]
,[NoteRtf]
,[NoteText]
,[IsDocumentManagement]
,[UserCreated]
,[DateCreated]
,[UserModified]
,[DateModified]
,[SourceDocumentCode_DEV000081]
FROM [CRMNote]
GO
Thanks for the reply!
Fawad Rashidi
www.fawadafr.com -
fawadafr (5/13/2010)
Would this be a good table definition?SELECT .... FROM [CRMNote]
GO
Thanks for the reply!
No, since the column definition is missing. There are no ready to use sample data either...
Please read and follow the article I mentioned before.
-
Once again, thank you very much for the reply Lutz. Here is the table structure as you requested:
Here is a sample result:
I was thinking of joining the CRMNote table with the Customer table on crmnote.entitycode = customer.entitycode to get the usercode of people who created the duplicate customer records. Then, EntityCodes that are created by usercode = 'fawadafr' will remain but EntityCodes that were created by other usercodes will be obsolete. Of course, prior obsoleting any duplicate record, I must move their notes by applying proper EntityCode and ContactCode.
Here is the structure of the customer table:
Once again, thank you very much for your help!
Fawad Rashidi
www.fawadafr.com -
Nice screen shots! Seems like you put quite some effort into it...
Unfortunately, that's not even close to what the article I mentioned describes as "ready to use data"... :crying:
Again, please take the time to read and follow the article I mentioned (the first one in my signature...)
-
I read the article couple times and I hope I got most of it done. If possible, please provide me with some instruction on how to get the rest of info from the SQL Management Studio as I am fairly new to this.
USE [ISImplantDirectTest]
GO
/****** Object: Table [dbo].[CRMNote] Script Date: 05/14/2010 14:20:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CRMNote](
[Counter] [int] IDENTITY(1,1) NOT NULL,
[NoteCode] [nvarchar](30) NOT NULL,
[EntityCode] [nvarchar](30) NULL,
[ContactCode] [nvarchar](30) NULL,
[ParentType] [nvarchar](50) NULL,
[OwnerCode] [nvarchar](30) NULL,
[Title] [nvarchar](100) NULL,
[NoteRtf] [ntext] NULL,
[NoteText] [ntext] NULL,
[IsDocumentManagement] [bit] NULL,
[UserCreated] [nvarchar](30) NULL,
[DateCreated] [datetime] NULL,
[UserModified] [nvarchar](30) NULL,
[DateModified] [datetime] NULL,
[SourceDocumentCode_DEV000081] [nvarchar](30) NULL,
CONSTRAINT [PK_CRMNote] PRIMARY KEY CLUSTERED
(
[NoteCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The note ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'NoteCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The entity ID: Customer, Supplier, Activity, etc' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'EntityCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The contact ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'ContactCode'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Determines the entity that owns the note: CustomerContact, CRMActivity, etc.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'ParentType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The note''s subject' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'Title'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The actual note in RTF format' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'NoteRtf'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The actual note in plain text' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote', @level2type=N'COLUMN',@level2name=N'NoteText'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Stores all notes of the company' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CRMNote'
GO
ALTER TABLE [dbo].[CRMNote] WITH NOCHECK ADD CONSTRAINT [FK_CRMNote_CRMContact] FOREIGN KEY([ContactCode])
REFERENCES [dbo].[CRMContact] ([ContactCode])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CRMNote] CHECK CONSTRAINT [FK_CRMNote_CRMContact]
GO
ALTER TABLE [dbo].[CRMNote] ADD CONSTRAINT [DF_CRMNote_NoteCode] DEFAULT (N'[To be Generated]') FOR [NoteCode]
GO
Here are some INSERT code sniped:
INSERT INTO [ISImplantDirectTest].[dbo].[CRMNote]
([NoteCode]
,[EntityCode]
,[ContactCode]
,[ParentType]
,[OwnerCode]
,[Title]
,[NoteRtf]
,[NoteText]
,[IsDocumentManagement]
,[UserCreated]
,[DateCreated]
,[UserModified]
,[DateModified]
,[SourceDocumentCode_DEV000081])
VALUES
(<NoteCode, nvarchar(30),>
,<EntityCode, nvarchar(30),>
,<ContactCode, nvarchar(30),>
,<ParentType, nvarchar(50),>
,<OwnerCode, nvarchar(30),>
,<Title, nvarchar(100),>
,<NoteRtf, ntext,>
,<NoteText, ntext,>
,<IsDocumentManagement, bit,>
,<UserCreated, nvarchar(30),>
,<DateCreated, datetime,>
,<UserModified, nvarchar(30),>
,<DateModified, datetime,>
,<SourceDocumentCode_DEV000081, nvarchar(30),>)
GO
Here are some UPDATE statements:
UPDATE [ISImplantDirectTest].[dbo].[CRMNote]
SET [NoteCode] = <NoteCode, nvarchar(30),>
,[EntityCode] = <EntityCode, nvarchar(30),>
,[ContactCode] = <ContactCode, nvarchar(30),>
,[ParentType] = <ParentType, nvarchar(50),>
,[OwnerCode] = <OwnerCode, nvarchar(30),>
,[Title] = <Title, nvarchar(100),>
,[NoteRtf] = <NoteRtf, ntext,>
,[NoteText] = <NoteText, ntext,>
,[IsDocumentManagement] = <IsDocumentManagement, bit,>
,[UserCreated] = <UserCreated, nvarchar(30),>
,[DateCreated] = <DateCreated, datetime,>
,[UserModified] = <UserModified, nvarchar(30),>
,[DateModified] = <DateModified, datetime,>
,[SourceDocumentCode_DEV000081] = <SourceDocumentCode_DEV000081, nvarchar(30),>
WHERE <Search Conditions,,>
GO
Fawad Rashidi
www.fawadafr.com -
To get help, you're best off giving table definitions for the tables in question *and* a simple script to do data insertions into those tables to allow people trying to help you the chance to simply copy and paste all of that into a query window.
I don't have sample data, but I think the below is basically what you're looking for. My logic makes the record of choice for any given customer the one with the lowest [Counter] ID. You can change that to whatever you want.
update crmnoteset EntityCode = c_o.CustomerCode,
ContractCode = c_o.DefaultContact
from crmnote cn
join customer c
on c.CustomerCode = cn.EntityCode
and c.DefaultContact = cn.DefaultContact
join customer c_o
on c_o.[Counter] =
(
select min(sq.[Counter])
from Customer sq
where sq.CustomerName = c.CustomerName
and sq.[Address] = c.[Address]
and sq.City = c.City
and sq.[State] = sq.[State]
)
I personally do not like deleting records like these in the customers table. If you do, test very thoroughly before you run it. My preference is to add a bit field 'IsActive' to keep track of which ones are in use. Thus, a 1 would be an active record and a 0 would be an inactive record. If you do that, this code could be used to update that table:
update customerset IsActive = 0
from customer c
where exists
(
select sq.[Counter]
from customer sq
where sq.[Counter] < c.[Counter]
and sq.CustomerName = c.CustomerName
and sq.[Address] = c.[Address]
and sq.City = c.City
and sq.[State] = sq.[State]
)
Again, I didn't have data to test against so please use at your own risk after doing your own testing.
-
Thank you very much for your help! I will study your code and try to execute it on my end. 🙂
Fawad Rashidi
www.fawadafr.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply