Post reply

I desperately need query optimization: query already runs for 1.5 days.

  • November 10, 2011 at 9:29 am

    #251531

    I have a script that removes duplicates from prospects table, update column values of duplicate records and updates references in the prospectsincampaigns table.

    prospects holds 111.000 records

    prospectsincampaigns holds 160.000 records

    The script to generate the tables is below as well as the script that I run to perform the above action.

    The problem is that the script is already running 1.5 DAY! and still is not finished...so I wonder, without losing functionality in the script, is there a way to increase performance?

    Thanks!

    ************ CREATE TABLES ************

    USE [yba]

    GO

    /****** Object: Table [dbo].[ProspectsInCampaigns] Script Date: 11/10/2011 17:23:41 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[ProspectsInCampaigns](

    [prospectid] [int] NOT NULL,

    [campaignid] [int] NOT NULL,

    [result] [int] NOT NULL,

    [personalcallbackid] [nvarchar](50) NULL,

    [callbackdate] [datetime] NOT NULL,

    [appointmentdate] [datetime] NOT NULL,

    [notes] [nvarchar](4000) NULL,

    [updatedate] [datetime] NOT NULL,

    [updatedby] [nvarchar](50) NULL,

    [recordlastrequestedby] [nvarchar](50) NULL,

    [recordlastrequestdate] [datetime] NOT NULL,

    [exportdate] [datetime] NOT NULL,

    [createdate] [datetime] NOT NULL

    ) ON [PRIMARY]

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1=mobiel,2=vast,3=managed service,4=0800,5=0900,6=digitale pen' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProspectsInCampaigns', @level2type=N'COLUMN',@level2name=N'notes'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'user who last updated this record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProspectsInCampaigns', @level2type=N'COLUMN',@level2name=N'updatedby'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'who is the last user who requested this record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProspectsInCampaigns', @level2type=N'COLUMN',@level2name=N'recordlastrequestedby'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'who is the last user who requested this record' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProspectsInCampaigns', @level2type=N'COLUMN',@level2name=N'recordlastrequestdate'

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'when was this record exported to excel' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProspectsInCampaigns', @level2type=N'COLUMN',@level2name=N'exportdate'

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_result] DEFAULT ((0)) FOR [result]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_callbackdate] DEFAULT (((1)/(1))/(1900)) FOR [callbackdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_appointmentdate] DEFAULT (((1)/(1))/(1900)) FOR [appointmentdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_updatedate] DEFAULT (((1)/(1))/(1900)) FOR [updatedate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_recordlastrequestdate] DEFAULT (((1)/(1))/(1900)) FOR [recordlastrequestdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_exportdate] DEFAULT (((1)/(1))/(1900)) FOR [exportdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns] ADD CONSTRAINT [DF_ProspectsInCampaigns_createdate] DEFAULT (getdate()) FOR [createdate]

    GO

    USE [yba]

    GO

    /****** Object: Table [dbo].[prospects] Script Date: 11/10/2011 17:23:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[prospects](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [uuid] [uniqueidentifier] NOT NULL,

    [companyname] [nvarchar](255) NULL,

    [companytype] [int] NOT NULL,

    [companyemployees] [int] NOT NULL,

    [companyaddress] [nvarchar](255) NULL,

    [companyhousenr] [nvarchar](255) NULL,

    [companyhousenradd] [nvarchar](255) NULL,

    [companyzipcode] [nvarchar](255) NULL,

    [companycity] [nvarchar](255) NULL,

    [companyfax] [nvarchar](255) NULL,

    [companybranche] [tinyint] NOT NULL,

    [companyphone] [nvarchar](255) NULL,

    [companywebsite] [nvarchar](255) NULL,

    [companyvehicles] [int] NOT NULL,

    [cp_sex] [int] NOT NULL,

    [cp_initials] [nvarchar](255) NULL,

    [cp_firstname] [nvarchar](255) NULL,

    [cp_middlename] [nvarchar](255) NULL,

    [cp_lastname] [nvarchar](255) NULL,

    [cp_jobtitle] [nvarchar](255) NULL,

    [cp_email] [nvarchar](255) NULL,

    [cp_phone] [nvarchar](255) NULL,

    [cp_phone2] [nvarchar](255) NULL,

    [cp_phonemobile] [nvarchar](255) NULL,

    [telco_mob] [nvarchar](255) NULL,

    [telco_mob_connections] [int] NULL,

    [telco_mob_provider] [nvarchar](255) NULL,

    [telco_mob_dealer] [nvarchar](255) NULL,

    [telco_mob_enddate] [datetime] NULL,

    [telco_mob_costs] [int] NULL,

    [telco_vast] [nvarchar](255) NULL,

    [telco_vast_connections] [int] NULL,

    [telco_vast_provider] [nvarchar](255) NULL,

    [telco_vast_dealer] [nvarchar](255) NULL,

    [telco_vast_enddate] [datetime] NULL,

    [telco_vast_costs] [int] NULL,

    [telco_data] [nvarchar](255) NULL,

    [telco_data_connections] [int] NULL,

    [telco_data_provider] [nvarchar](255) NULL,

    [telco_data_dealer] [nvarchar](255) NULL,

    [telco_data_enddate] [datetime] NULL,

    [telco_data_costs] [int] NULL,

    [mraqua_interest] [nvarchar](50) NULL,

    [mraqua_coolers] [int] NOT NULL,

    [contactmoments] [nvarchar](4000) NULL,

    [importdate] [datetime] NULL,

    [filename] [nvarchar](255) NULL,

    [presenter_interest] [nvarchar](500) NULL,

    [presenter_budget] [nvarchar](50) NULL,

    [campaignid_delme] [int] NOT NULL,

    [importcode] [nvarchar](50) NULL,

    [campaign1] [varchar](255) NULL,

    [campaign2] [varchar](255) NULL,

    [campaign3] [varchar](255) NULL,

    [campaign4] [varchar](255) NULL,

    [campaign5] [varchar](255) NULL,

    [campaign6] [varchar](255) NULL,

    [campaign7] [varchar](255) NULL,

    [campaign8] [varchar](255) NULL,

    [campaign9] [varchar](255) NULL,

    [campaign10] [varchar](255) NULL,

    [campaign11] [varchar](255) NULL,

    [campaign12] [varchar](255) NULL,

    [campaign13] [varchar](255) NULL,

    [campaign14] [varchar](255) NULL,

    [campaign15] [varchar](255) NULL,

    [campaign16] [varchar](255) NULL,

    [campaign17] [varchar](255) NULL,

    [campaign18] [varchar](255) NULL,

    [campaign19] [varchar](255) NULL,

    [campaign20] [varchar](255) NULL,

    [campaign21] [varchar](255) NULL,

    [campaign22] [varchar](255) NULL,

    [campaign23] [varchar](255) NULL,

    [campaign24] [varchar](255) NULL,

    [campaign25] [varchar](255) NULL,

    [campaign26] [varchar](255) NULL,

    [campaign27] [varchar](255) NULL,

    [campaign28] [varchar](255) NULL,

    [campaign29] [varchar](255) NULL,

    [campaign30] [varchar](255) NULL,

    [campaign31] [varchar](255) NULL,

    [campaign32] [varchar](255) NULL,

    [campaign33] [varchar](255) NULL,

    [campaign34] [varchar](255) NULL,

    [campaign35] [varchar](255) NULL,

    [campaign36] [varchar](255) NULL,

    [campaign37] [varchar](255) NULL,

    [campaign38] [varchar](255) NULL,

    [campaign39] [varchar](255) NULL,

    [campaign40] [varchar](255) NULL,

    [campaign41] [varchar](255) NULL,

    [campaign42] [varchar](255) NULL,

    [campaign43] [varchar](255) NULL,

    [campaign44] [varchar](255) NULL,

    [campaign45] [varchar](255) NULL,

    [campaign46] [varchar](255) NULL,

    [campaign47] [varchar](255) NULL,

    [campaign48] [varchar](255) NULL,

    [campaign49] [varchar](255) NULL,

    [campaign50] [varchar](255) NULL,

    [campaign51] [varchar](255) NULL,

    [campaign52] [varchar](255) NULL,

    [campaign53] [varchar](255) NULL,

    [campaign54] [varchar](255) NULL,

    [campaign55] [varchar](255) NULL,

    [campaign56] [varchar](255) NULL,

    [campaign57] [varchar](255) NULL,

    [campaign58] [varchar](255) NULL,

    [campaign59] [varchar](255) NULL,

    [campaign60] [varchar](255) NULL,

    [campaign61] [varchar](255) NULL,

    [campaign62] [varchar](255) NULL,

    [campaign63] [varchar](255) NULL,

    [campaign64] [varchar](255) NULL,

    [campaign65] [varchar](255) NULL,

    [campaign66] [varchar](255) NULL,

    [campaign67] [varchar](255) NULL,

    [campaign68] [varchar](255) NULL,

    [campaign69] [varchar](255) NULL,

    [campaign70] [varchar](255) NULL,

    [campaign71] [varchar](255) NULL,

    [campaign72] [varchar](255) NULL,

    [campaign73] [varchar](255) NULL,

    [campaign74] [varchar](255) NULL,

    [campaign75] [varchar](255) NULL,

    [campaign76] [varchar](255) NULL,

    [campaign77] [varchar](255) NULL,

    [campaign78] [varchar](255) NULL,

    [campaign79] [varchar](255) NULL,

    [campaign80] [varchar](255) NULL,

    [campaign81] [varchar](255) NULL,

    [campaign82] [varchar](255) NULL,

    [campaign83] [varchar](255) NULL,

    [campaign84] [varchar](255) NULL,

    [campaign85] [varchar](255) NULL,

    [campaign86] [varchar](255) NULL,

    [campaign87] [varchar](255) NULL,

    [campaign88] [varchar](255) NULL,

    [campaign89] [varchar](255) NULL,

    [campaign90] [varchar](255) NULL,

    [campaign91] [varchar](255) NULL,

    [campaign92] [varchar](255) NULL,

    [campaign93] [varchar](255) NULL,

    [campaign94] [varchar](255) NULL,

    [campaign95] [varchar](255) NULL,

    [campaign96] [varchar](255) NULL,

    [campaign97] [varchar](255) NULL,

    [campaign98] [varchar](255) NULL,

    [campaign99] [varchar](255) NULL,

    [campaign100] [varchar](255) NULL,

    [appointmentdate1] [date] NULL,

    [appointmentdate2] [date] NULL,

    [appointmentdate3] [date] NULL,

    [appointmentdate4] [date] NULL,

    [appointmentdate5] [date] NULL,

    [appointmentdate6] [date] NULL,

    [appointmentdate7] [date] NULL,

    [appointmentdate8] [date] NULL,

    [appointmentdate9] [date] NULL,

    [appointmentdate10] [date] NULL,

    [appointmentdate11] [date] NULL,

    [appointmentdate12] [date] NULL,

    [appointmentdate13] [date] NULL,

    [appointmentdate14] [date] NULL,

    [appointmentdate15] [date] NULL,

    [appointmentdate16] [date] NULL,

    [appointmentdate17] [date] NULL,

    [appointmentdate18] [date] NULL,

    [appointmentdate19] [date] NULL,

    [appointmentdate20] [date] NULL,

    [appointmentdate21] [date] NULL,

    [appointmentdate22] [date] NULL,

    [appointmentdate23] [date] NULL,

    [appointmentdate24] [date] NULL,

    [appointmentdate25] [date] NULL,

    [appointmentdate26] [date] NULL,

    [appointmentdate27] [date] NULL,

    [appointmentdate28] [date] NULL,

    [appointmentdate29] [date] NULL,

    [appointmentdate30] [date] NULL,

    [appointmentdate31] [date] NULL,

    [appointmentdate32] [date] NULL,

    [appointmentdate33] [date] NULL,

    [appointmentdate34] [date] NULL,

    [appointmentdate35] [date] NULL,

    [appointmentdate36] [date] NULL,

    [appointmentdate37] [date] NULL,

    [appointmentdate38] [date] NULL,

    [appointmentdate39] [date] NULL,

    [appointmentdate40] [date] NULL,

    [appointmentdate41] [date] NULL,

    [appointmentdate42] [date] NULL,

    [appointmentdate43] [date] NULL,

    [appointmentdate44] [date] NULL,

    [appointmentdate45] [date] NULL,

    [appointmentdate46] [date] NULL,

    [appointmentdate47] [date] NULL,

    [appointmentdate48] [date] NULL,

    [appointmentdate49] [date] NULL,

    [appointmentdate50] [date] NULL,

    [appointmentdate51] [date] NULL,

    [appointmentdate52] [date] NULL,

    [appointmentdate53] [date] NULL,

    [appointmentdate54] [date] NULL,

    [appointmentdate55] [date] NULL,

    [appointmentdate56] [date] NULL,

    [appointmentdate57] [date] NULL,

    [appointmentdate58] [date] NULL,

    [appointmentdate59] [date] NULL,

    [appointmentdate60] [date] NULL,

    [appointmentdate61] [date] NULL,

    [appointmentdate62] [date] NULL,

    [appointmentdate63] [date] NULL,

    [appointmentdate64] [date] NULL,

    [appointmentdate65] [date] NULL,

    [appointmentdate66] [date] NULL,

    [appointmentdate67] [date] NULL,

    [appointmentdate68] [date] NULL,

    [appointmentdate69] [date] NULL,

    [appointmentdate70] [date] NULL,

    [appointmentdate71] [date] NULL,

    [appointmentdate72] [date] NULL,

    [appointmentdate73] [date] NULL,

    [appointmentdate74] [date] NULL,

    [appointmentdate75] [date] NULL,

    [appointmentdate76] [date] NULL,

    [appointmentdate77] [date] NULL,

    [appointmentdate78] [date] NULL,

    [appointmentdate79] [date] NULL,

    [appointmentdate80] [date] NULL,

    [appointmentdate81] [date] NULL,

    [appointmentdate82] [date] NULL,

    [appointmentdate83] [date] NULL,

    [appointmentdate84] [date] NULL,

    [appointmentdate85] [date] NULL,

    [appointmentdate86] [date] NULL,

    [appointmentdate87] [date] NULL,

    [appointmentdate88] [date] NULL,

    [appointmentdate89] [date] NULL,

    [appointmentdate90] [date] NULL,

    [appointmentdate91] [date] NULL,

    [appointmentdate92] [date] NULL,

    [appointmentdate93] [date] NULL,

    [appointmentdate94] [date] NULL,

    [appointmentdate95] [date] NULL,

    [appointmentdate96] [date] NULL,

    [appointmentdate97] [date] NULL,

    [appointmentdate98] [date] NULL,

    [appointmentdate99] [date] NULL,

    [appointmentdate100] [date] NULL,

    CONSTRAINT [PK_intercity] PRIMARY KEY CLUSTERED

    (

    [id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'this is a unique code that can be given to the import, so that its easy to find the imported records and also assign them to campaigns based on this code' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'prospects', @level2type=N'COLUMN',@level2name=N'importcode'

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_uuid_1] DEFAULT (newid()) FOR [uuid]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_companytype_1] DEFAULT ((0)) FOR [companytype]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_companyemployees_1] DEFAULT ((0)) FOR [companyemployees]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_companybranche] DEFAULT ((0)) FOR [companybranche]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_companyvehicles] DEFAULT ((0)) FOR [companyvehicles]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_cp_sex_1] DEFAULT ((-1)) FOR [cp_sex]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_mob_connections] DEFAULT ((0)) FOR [telco_mob_connections]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_mob_enddate] DEFAULT (((1)/(1))/(1900)) FOR [telco_mob_enddate]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_mob_costs] DEFAULT ((0)) FOR [telco_mob_costs]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_vast_connections] DEFAULT ((0)) FOR [telco_vast_connections]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_vast_enddate] DEFAULT (((1)/(1))/(1900)) FOR [telco_vast_enddate]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_vast_costs] DEFAULT ((0)) FOR [telco_vast_costs]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_data_connections] DEFAULT ((0)) FOR [telco_data_connections]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_data_enddate] DEFAULT (((1)/(1))/(1900)) FOR [telco_data_enddate]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_telco_data_costs] DEFAULT ((0)) FOR [telco_data_costs]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_mraqua_coolers_1] DEFAULT ((0)) FOR [mraqua_coolers]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_importdate] DEFAULT (getdate()) FOR [importdate]

    GO

    ALTER TABLE [dbo].[prospects] ADD CONSTRAINT [DF_prospects_campaignid] DEFAULT ((0)) FOR [campaignid_delme]

    GO

    ************ SCRIPT WHICH NEEDS TO BE OPTIMIZED ************

    ---------------------------------Number 1, 2, 3, 3a, 3b------------------------------------

    DECLARE @companyPhoneBeingChecked nvarchar(255)

    DECLARE @companyPhonePossibleDup nvarchar(255)

    DECLARE @companyNameBeingChecked nvarchar(255)

    DECLARE @companyNamePossibleDup nvarchar(255)

    DECLARE @IdBeingChecked Int

    DECLARE @DuplicateId Int

    DECLARE @MinimumId Int

    SET @MinimumId = 0

    --in companyphone spaces and - may be ignored. So: 020-12345678 is the same

    --number as 02012345678 and the same as 020-123 45 678

    DECLARE dup_check CURSOR FAST_FORWARD FOR SELECT REPLACE(REPLACE(REPLACE(REPLACE(companyphone,'(',''),' ',''),'-',''),')',''), companyname, id FROM prospects

    OPEN dup_check

    FETCH NEXT FROM dup_check INTO @companyPhoneBeingChecked, @companyNameBeingChecked, @IdBeingChecked

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE scan_rows CURSOR FAST_FORWARD FOR SELECT REPLACE(REPLACE(REPLACE(REPLACE(companyphone,'(',''),' ',''),'-',''),')',''), companyname FROM prospects

    OPEN scan_rows

    FETCH NEXT FROM scan_rows INTO @companyPhonePossibleDup, @companyNamePossibleDup

    WHILE @@FETCH_STATUS = 0

    BEGIN

    ----If records have the exact same companyphone value and companyphone is not empty (‘’)

    ----and companyphone is not null.

    IF (@companyPhoneBeingChecked = @companyPhonePossibleDup)

    AND (@companyPhoneBeingChecked != '' or @companyPhoneBeingChecked != null)

    BEGIN

    ----duplicate records needs to become the same. For those records the following may be done:

    ----the highest value of a column within this duplicate set may be used for all duplicate prospects

    update prospects set companytype = (select max(companytype) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set companyemployees = (select max(companyemployees) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set companyvehicles = (select max(companyvehicles) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set companyname = (select max(companyname) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_sex = (select max(cp_sex) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_initials = (select max(cp_initials) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_firstname = (select max(cp_firstname) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_middlename = (select max(cp_middlename) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_lastname = (select max(cp_lastname) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_jobtitle = (select max(cp_jobtitle) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_email = (select max(cp_email) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_phone = (select max(cp_phone) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_phone2 = (select max(cp_phone2) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set cp_phonemobile = (select max(cp_phonemobile) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob = (select max(telco_mob) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob_connections = (select max(telco_mob_connections) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob_provider = (select max(telco_mob_provider) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob_dealer = (select max(telco_mob_dealer) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob_enddate = (select max(telco_mob_enddate) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_mob_costs = (select max(telco_mob_costs) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast = (select max(telco_vast) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast_connections = (select max(telco_vast_connections) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast_provider = (select max(telco_vast_provider) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast_dealer = (select max(telco_vast_dealer) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast_enddate = (select max(telco_vast_enddate) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_vast_costs = (select max(telco_vast_costs) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data = (select max(telco_data) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data_connections = (select max(telco_data_connections) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data_provider = (select max(telco_data_provider) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data_dealer = (select max(telco_data_dealer) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data_enddate = (select max(telco_data_enddate) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set telco_data_costs = (select max(telco_data_costs) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set importdate = (select max(importdate) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set filename = (select max(filename) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set presenter_interest = (select max(presenter_interest) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set presenter_budget = (select max(presenter_budget) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set campaignid_delme = (select max(campaignid_delme) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    update prospects set importcode = (select max(importcode) from prospects where companyphone = @companyPhonePossibleDup) where companyphone = @companyPhonePossibleDup

    --Get the record with lowest id of this set of duplicates: record X.

    set @MinimumId = (select MIN(id) from prospects where companyphone = @companyPhonePossibleDup)

    --Update all references of [prospectsincampaigns].prospectid that point to a record in setY to point to recordX

    update prospectsincampaigns set prospectid = @MinimumId where prospectid

    in (select id from prospects where companyphone = @companyPhonePossibleDup)

    -- in setY in [prospects], delete all duplicates except recordX

    delete from prospects where (companyphone = @companyPhonePossibleDup and id <> @MinimumId)

    END

    ElSE IF (@companyPhoneBeingChecked = '' or @companyPhoneBeingChecked = null)

    AND (lower(@companyNameBeingChecked) = lower(@companyNamePossibleDup) or upper(@companyNameBeingChecked) = upper(@companyNamePossibleDup))

    BEGIN

    ----duplicate records needs to become the same. For those records the following may be done:

    ----the highest value of a column within this duplicate set may be used for all duplicate prospects

    update prospects set companytype = (select max(companytype) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set companyemployees = (select max(companyemployees) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set companyvehicles = (select max(companyvehicles) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_sex = (select max(cp_sex) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_initials = (select max(cp_initials) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_firstname = (select max(cp_firstname) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_middlename = (select max(cp_middlename) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_lastname = (select max(cp_lastname) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_jobtitle = (select max(cp_jobtitle) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_email = (select max(cp_email) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_phone = (select max(cp_phone) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_phone2 = (select max(cp_phone2) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set cp_phonemobile = (select max(cp_phonemobile) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob = (select max(telco_mob) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob_connections = (select max(telco_mob_connections) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob_provider = (select max(telco_mob_provider) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob_dealer = (select max(telco_mob_dealer) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob_enddate = (select max(telco_mob_enddate) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_mob_costs = (select max(telco_mob_costs) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast = (select max(telco_vast) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast_connections = (select max(telco_vast_connections) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast_provider = (select max(telco_vast_provider) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast_dealer = (select max(telco_vast_dealer) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast_enddate = (select max(telco_vast_enddate) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_vast_costs = (select max(telco_vast_costs) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data = (select max(telco_data) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data_connections = (select max(telco_data_connections) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data_provider = (select max(telco_data_provider) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data_dealer = (select max(telco_data_dealer) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data_enddate = (select max(telco_data_enddate) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set telco_data_costs = (select max(telco_data_costs) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set importdate = (select max(importdate) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set filename = (select max(filename) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set presenter_interest = (select max(presenter_interest) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set presenter_budget = (select max(presenter_budget) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set campaignid_delme = (select max(campaignid_delme) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    update prospects set importcode = (select max(importcode) from prospects where companyphone = @companyNamePossibleDup) where companyphone = @companyNamePossibleDup

    --Get the record with lowest id of this set of duplicates: record X.

    set @MinimumId = (select MIN(id) from prospects where companyname = @companyNamePossibleDup)

    --Update all references of [prospectsincampaigns].prospectid that point to a record in setY to point to recordX

    update prospectsincampaigns set prospectid = @MinimumId where prospectid

    in (select id from prospects where companyname = @companyNamePossibleDup)

    -- in setY in [prospects], delete all duplicates except recordX

    delete from prospects where (companyname = @companyNamePossibleDup and id <> @MinimumId)

    END

    FETCH NEXT FROM scan_rows INTO @companyPhonePossibleDup, @companyNamePossibleDup

    END

    --delete records from [prospects] for which prospectsincampaigns.result=26 or prospectsincampaigns.result=13

    delete from prospects where id in (select prospectid from ProspectsInCampaigns where result IN (26,13))

    --delete records from [prospectsincampaigns] which have any of these resultcodes: 7,2,4,25,23,3,26,13

    delete from prospectsincampaigns where result IN (7,2,4,25,23,3,26,13)

    CLOSE scan_rows

    DEALLOCATE scan_rows

    FETCH NEXT FROM dup_check INTO @companyPhoneBeingChecked, @companyNameBeingChecked, @IdBeingChecked

    END

    CLOSE dup_check

    DEALLOCATE dup_check

  • November 10, 2011 at 9:33 am

    #1405873

    Reformatted for sanity!

    USE [yba]

    GO

    /****** Object: Table [dbo].[ProspectsInCampaigns] Script Date: 11/10/2011 17:23:41 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[ProspectsInCampaigns]

    (

    [prospectid] [int] NOT NULL

    , [campaignid] [int] NOT NULL

    , [result] [int] NOT NULL

    , [personalcallbackid] [nvarchar](50) NULL

    , [callbackdate] [datetime] NOT NULL

    , [appointmentdate] [datetime] NOT NULL

    , [notes] [nvarchar](4000) NULL

    , [updatedate] [datetime] NOT NULL

    , [updatedby] [nvarchar](50) NULL

    , [recordlastrequestedby] [nvarchar](50) NULL

    , [recordlastrequestdate] [datetime] NOT NULL

    , [exportdate] [datetime] NOT NULL

    , [createdate] [datetime] NOT NULL

    )

    ON

    [PRIMARY]

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'1=mobiel,2=vast,3=managed service,4=0800,5=0900,6=digitale pen' ,

    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'ProspectsInCampaigns' , @level2type = N'COLUMN' ,

    @level2name = N'notes'

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'user who last updated this record' , @level0type = N'SCHEMA' ,

    @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'ProspectsInCampaigns' , @level2type = N'COLUMN' ,

    @level2name = N'updatedby'

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'who is the last user who requested this record' ,

    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'ProspectsInCampaigns' , @level2type = N'COLUMN' ,

    @level2name = N'recordlastrequestedby'

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'who is the last user who requested this record' ,

    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'ProspectsInCampaigns' , @level2type = N'COLUMN' ,

    @level2name = N'recordlastrequestdate'

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'when was this record exported to excel' ,

    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'ProspectsInCampaigns' , @level2type = N'COLUMN' ,

    @level2name = N'exportdate'

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_result] DEFAULT ( (0) ) FOR [result]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_callbackdate] DEFAULT ( ( ( 1 ) / ( 1 ) ) / ( 1900 ) )

    FOR [callbackdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_appointmentdate] DEFAULT ( ( ( 1 ) / ( 1 ) ) / ( 1900 ) )

    FOR [appointmentdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_updatedate] DEFAULT ( ( ( 1 ) / ( 1 ) ) / ( 1900 ) )

    FOR [updatedate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_recordlastrequestdate] DEFAULT ( ( ( 1 ) / ( 1 ) ) / ( 1900 ) )

    FOR [recordlastrequestdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_exportdate] DEFAULT ( ( ( 1 ) / ( 1 ) ) / ( 1900 ) )

    FOR [exportdate]

    GO

    ALTER TABLE [dbo].[ProspectsInCampaigns]

    ADD

    CONSTRAINT [DF_ProspectsInCampaigns_createdate] DEFAULT ( getdate() ) FOR [createdate]

    GO

    USE [yba]

    GO

    /****** Object: Table [dbo].[prospects] Script Date: 11/10/2011 17:23:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[prospects]

    (

    [id] [int] IDENTITY(1 , 1)

    NOT NULL

    , [uuid] [uniqueidentifier] NOT NULL

    , [companyname] [nvarchar](255) NULL

    , [companytype] [int] NOT NULL

    , [companyemployees] [int] NOT NULL

    , [companyaddress] [nvarchar](255) NULL

    , [companyhousenr] [nvarchar](255) NULL

    , [companyhousenradd] [nvarchar](255) NULL

    , [companyzipcode] [nvarchar](255) NULL

    , [companycity] [nvarchar](255) NULL

    , [companyfax] [nvarchar](255) NULL

    , [companybranche] [tinyint] NOT NULL

    , [companyphone] [nvarchar](255) NULL

    , [companywebsite] [nvarchar](255) NULL

    , [companyvehicles] [int] NOT NULL

    , [cp_sex] [int] NOT NULL

    , [cp_initials] [nvarchar](255) NULL

    , [cp_firstname] [nvarchar](255) NULL

    , [cp_middlename] [nvarchar](255) NULL

    , [cp_lastname] [nvarchar](255) NULL

    , [cp_jobtitle] [nvarchar](255) NULL

    , [cp_email] [nvarchar](255) NULL

    , [cp_phone] [nvarchar](255) NULL

    , [cp_phone2] [nvarchar](255) NULL

    , [cp_phonemobile] [nvarchar](255) NULL

    , [telco_mob] [nvarchar](255) NULL

    , [telco_mob_connections] [int] NULL

    , [telco_mob_provider] [nvarchar](255) NULL

    , [telco_mob_dealer] [nvarchar](255) NULL

    , [telco_mob_enddate] [datetime] NULL

    , [telco_mob_costs] [int] NULL

    , [telco_vast] [nvarchar](255) NULL

    , [telco_vast_connections] [int] NULL

    , [telco_vast_provider] [nvarchar](255) NULL

    , [telco_vast_dealer] [nvarchar](255) NULL

    , [telco_vast_enddate] [datetime] NULL

    , [telco_vast_costs] [int] NULL

    , [telco_data] [nvarchar](255) NULL

    , [telco_data_connections] [int] NULL

    , [telco_data_provider] [nvarchar](255) NULL

    , [telco_data_dealer] [nvarchar](255) NULL

    , [telco_data_enddate] [datetime] NULL

    , [telco_data_costs] [int] NULL

    , [mraqua_interest] [nvarchar](50) NULL

    , [mraqua_coolers] [int] NOT NULL

    , [contactmoments] [nvarchar](4000) NULL

    , [importdate] [datetime] NULL

    , [filename] [nvarchar](255) NULL

    , [presenter_interest] [nvarchar](500) NULL

    , [presenter_budget] [nvarchar](50) NULL

    , [campaignid_delme] [int] NOT NULL

    , [importcode] [nvarchar](50) NULL

    , [campaign1] [varchar](255) NULL

    , [campaign2] [varchar](255) NULL

    , [campaign3] [varchar](255) NULL

    , [campaign4] [varchar](255) NULL

    , [campaign5] [varchar](255) NULL

    , [campaign6] [varchar](255) NULL

    , [campaign7] [varchar](255) NULL

    , [campaign8] [varchar](255) NULL

    , [campaign9] [varchar](255) NULL

    , [campaign10] [varchar](255) NULL

    , [campaign11] [varchar](255) NULL

    , [campaign12] [varchar](255) NULL

    , [campaign13] [varchar](255) NULL

    , [campaign14] [varchar](255) NULL

    , [campaign15] [varchar](255) NULL

    , [campaign16] [varchar](255) NULL

    , [campaign17] [varchar](255) NULL

    , [campaign18] [varchar](255) NULL

    , [campaign19] [varchar](255) NULL

    , [campaign20] [varchar](255) NULL

    , [campaign21] [varchar](255) NULL

    , [campaign22] [varchar](255) NULL

    , [campaign23] [varchar](255) NULL

    , [campaign24] [varchar](255) NULL

    , [campaign25] [varchar](255) NULL

    , [campaign26] [varchar](255) NULL

    , [campaign27] [varchar](255) NULL

    , [campaign28] [varchar](255) NULL

    , [campaign29] [varchar](255) NULL

    , [campaign30] [varchar](255) NULL

    , [campaign31] [varchar](255) NULL

    , [campaign32] [varchar](255) NULL

    , [campaign33] [varchar](255) NULL

    , [campaign34] [varchar](255) NULL

    , [campaign35] [varchar](255) NULL

    , [campaign36] [varchar](255) NULL

    , [campaign37] [varchar](255) NULL

    , [campaign38] [varchar](255) NULL

    , [campaign39] [varchar](255) NULL

    , [campaign40] [varchar](255) NULL

    , [campaign41] [varchar](255) NULL

    , [campaign42] [varchar](255) NULL

    , [campaign43] [varchar](255) NULL

    , [campaign44] [varchar](255) NULL

    , [campaign45] [varchar](255) NULL

    , [campaign46] [varchar](255) NULL

    , [campaign47] [varchar](255) NULL

    , [campaign48] [varchar](255) NULL

    , [campaign49] [varchar](255) NULL

    , [campaign50] [varchar](255) NULL

    , [campaign51] [varchar](255) NULL

    , [campaign52] [varchar](255) NULL

    , [campaign53] [varchar](255) NULL

    , [campaign54] [varchar](255) NULL

    , [campaign55] [varchar](255) NULL

    , [campaign56] [varchar](255) NULL

    , [campaign57] [varchar](255) NULL

    , [campaign58] [varchar](255) NULL

    , [campaign59] [varchar](255) NULL

    , [campaign60] [varchar](255) NULL

    , [campaign61] [varchar](255) NULL

    , [campaign62] [varchar](255) NULL

    , [campaign63] [varchar](255) NULL

    , [campaign64] [varchar](255) NULL

    , [campaign65] [varchar](255) NULL

    , [campaign66] [varchar](255) NULL

    , [campaign67] [varchar](255) NULL

    , [campaign68] [varchar](255) NULL

    , [campaign69] [varchar](255) NULL

    , [campaign70] [varchar](255) NULL

    , [campaign71] [varchar](255) NULL

    , [campaign72] [varchar](255) NULL

    , [campaign73] [varchar](255) NULL

    , [campaign74] [varchar](255) NULL

    , [campaign75] [varchar](255) NULL

    , [campaign76] [varchar](255) NULL

    , [campaign77] [varchar](255) NULL

    , [campaign78] [varchar](255) NULL

    , [campaign79] [varchar](255) NULL

    , [campaign80] [varchar](255) NULL

    , [campaign81] [varchar](255) NULL

    , [campaign82] [varchar](255) NULL

    , [campaign83] [varchar](255) NULL

    , [campaign84] [varchar](255) NULL

    , [campaign85] [varchar](255) NULL

    , [campaign86] [varchar](255) NULL

    , [campaign87] [varchar](255) NULL

    , [campaign88] [varchar](255) NULL

    , [campaign89] [varchar](255) NULL

    , [campaign90] [varchar](255) NULL

    , [campaign91] [varchar](255) NULL

    , [campaign92] [varchar](255) NULL

    , [campaign93] [varchar](255) NULL

    , [campaign94] [varchar](255) NULL

    , [campaign95] [varchar](255) NULL

    , [campaign96] [varchar](255) NULL

    , [campaign97] [varchar](255) NULL

    , [campaign98] [varchar](255) NULL

    , [campaign99] [varchar](255) NULL

    , [campaign100] [varchar](255) NULL

    , [appointmentdate1] [date] NULL

    , [appointmentdate2] [date] NULL

    , [appointmentdate3] [date] NULL

    , [appointmentdate4] [date] NULL

    , [appointmentdate5] [date] NULL

    , [appointmentdate6] [date] NULL

    , [appointmentdate7] [date] NULL

    , [appointmentdate8] [date] NULL

    , [appointmentdate9] [date] NULL

    , [appointmentdate10] [date] NULL

    , [appointmentdate11] [date] NULL

    , [appointmentdate12] [date] NULL

    , [appointmentdate13] [date] NULL

    , [appointmentdate14] [date] NULL

    , [appointmentdate15] [date] NULL

    , [appointmentdate16] [date] NULL

    , [appointmentdate17] [date] NULL

    , [appointmentdate18] [date] NULL

    , [appointmentdate19] [date] NULL

    , [appointmentdate20] [date] NULL

    , [appointmentdate21] [date] NULL

    , [appointmentdate22] [date] NULL

    , [appointmentdate23] [date] NULL

    , [appointmentdate24] [date] NULL

    , [appointmentdate25] [date] NULL

    , [appointmentdate26] [date] NULL

    , [appointmentdate27] [date] NULL

    , [appointmentdate28] [date] NULL

    , [appointmentdate29] [date] NULL

    , [appointmentdate30] [date] NULL

    , [appointmentdate31] [date] NULL

    , [appointmentdate32] [date] NULL

    , [appointmentdate33] [date] NULL

    , [appointmentdate34] [date] NULL

    , [appointmentdate35] [date] NULL

    , [appointmentdate36] [date] NULL

    , [appointmentdate37] [date] NULL

    , [appointmentdate38] [date] NULL

    , [appointmentdate39] [date] NULL

    , [appointmentdate40] [date] NULL

    , [appointmentdate41] [date] NULL

    , [appointmentdate42] [date] NULL

    , [appointmentdate43] [date] NULL

    , [appointmentdate44] [date] NULL

    , [appointmentdate45] [date] NULL

    , [appointmentdate46] [date] NULL

    , [appointmentdate47] [date] NULL

    , [appointmentdate48] [date] NULL

    , [appointmentdate49] [date] NULL

    , [appointmentdate50] [date] NULL

    , [appointmentdate51] [date] NULL

    , [appointmentdate52] [date] NULL

    , [appointmentdate53] [date] NULL

    , [appointmentdate54] [date] NULL

    , [appointmentdate55] [date] NULL

    , [appointmentdate56] [date] NULL

    , [appointmentdate57] [date] NULL

    , [appointmentdate58] [date] NULL

    , [appointmentdate59] [date] NULL

    , [appointmentdate60] [date] NULL

    , [appointmentdate61] [date] NULL

    , [appointmentdate62] [date] NULL

    , [appointmentdate63] [date] NULL

    , [appointmentdate64] [date] NULL

    , [appointmentdate65] [date] NULL

    , [appointmentdate66] [date] NULL

    , [appointmentdate67] [date] NULL

    , [appointmentdate68] [date] NULL

    , [appointmentdate69] [date] NULL

    , [appointmentdate70] [date] NULL

    , [appointmentdate71] [date] NULL

    , [appointmentdate72] [date] NULL

    , [appointmentdate73] [date] NULL

    , [appointmentdate74] [date] NULL

    , [appointmentdate75] [date] NULL

    , [appointmentdate76] [date] NULL

    , [appointmentdate77] [date] NULL

    , [appointmentdate78] [date] NULL

    , [appointmentdate79] [date] NULL

    , [appointmentdate80] [date] NULL

    , [appointmentdate81] [date] NULL

    , [appointmentdate82] [date] NULL

    , [appointmentdate83] [date] NULL

    , [appointmentdate84] [date] NULL

    , [appointmentdate85] [date] NULL

    , [appointmentdate86] [date] NULL

    , [appointmentdate87] [date] NULL

    , [appointmentdate88] [date] NULL

    , [appointmentdate89] [date] NULL

    , [appointmentdate90] [date] NULL

    , [appointmentdate91] [date] NULL

    , [appointmentdate92] [date] NULL

    , [appointmentdate93] [date] NULL

    , [appointmentdate94] [date] NULL

    , [appointmentdate95] [date] NULL

    , [appointmentdate96] [date] NULL

    , [appointmentdate97] [date] NULL

    , [appointmentdate98] [date] NULL

    , [appointmentdate99] [date] NULL

    , [appointmentdate100] [date] NULL

    , CONSTRAINT [PK_intercity] PRIMARY KEY CLUSTERED ( [id] ASC )

    WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF ,

    IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON )

    ON [PRIMARY]

    )

    ON

    [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    EXEC sys.sp_addextendedproperty @name = N'MS_Description' ,

    @value = N'this is a unique code that can be given to the import, so that its easy to find the imported records and also assign them to campaigns based on this code' ,

    @level0type = N'SCHEMA' , @level0name = N'dbo' , @level1type = N'TABLE' ,

    @level1name = N'prospects' , @level2type = N'COLUMN' ,

    @level2name = N'importcode'

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_uuid_1] DEFAULT ( newid() ) FOR [uuid]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_companytype_1] DEFAULT ( (0) ) FOR [companytype]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_companyemployees_1] DEFAULT ( (0) ) FOR [companyemployees]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_companybranche] DEFAULT ( (0) ) FOR [companybranche]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_companyvehicles] DEFAULT ( (0) ) FOR [companyvehicles]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_cp_sex_1] DEFAULT ( ( -1) ) FOR [cp_sex]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_mob_connections] DEFAULT ( (0) ) FOR [telco_mob_connections]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_mob_enddate] DEFAULT ( ( ( 1 ) / ( 1 ) )

    / ( 1900 ) ) FOR [telco_mob_enddate]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_mob_costs] DEFAULT ( (0) ) FOR [telco_mob_costs]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_vast_connections] DEFAULT ( (0) ) FOR [telco_vast_connections]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_vast_enddate] DEFAULT ( ( ( 1 ) / ( 1 ) )

    / ( 1900 ) ) FOR [telco_vast_enddate]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_vast_costs] DEFAULT ( (0) ) FOR [telco_vast_costs]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_data_connections] DEFAULT ( (0) ) FOR [telco_data_connections]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_data_enddate] DEFAULT ( ( ( 1 ) / ( 1 ) )

    / ( 1900 ) ) FOR [telco_data_enddate]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_telco_data_costs] DEFAULT ( (0) ) FOR [telco_data_costs]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_mraqua_coolers_1] DEFAULT ( (0) ) FOR [mraqua_coolers]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_importdate] DEFAULT ( getdate() ) FOR [importdate]

    GO

    ALTER TABLE [dbo].[prospects]

    ADD

    CONSTRAINT [DF_prospects_campaignid] DEFAULT ( (0) ) FOR [campaignid_delme]

    GO

    ---------------------------------Number 1, 2, 3, 3a, 3b------------------------------------

    DECLARE @companyPhoneBeingChecked nvarchar(255)

    DECLARE @companyPhonePossibleDup nvarchar(255)

    DECLARE @companyNameBeingChecked nvarchar(255)

    DECLARE @companyNamePossibleDup nvarchar(255)

    DECLARE @IdBeingChecked Int

    DECLARE @DuplicateId Int

    DECLARE @MinimumId Int

    SET @MinimumId = 0

    --in companyphone spaces and - may be ignored. So: 020-12345678 is the same

    --number as 02012345678 and the same as 020-123 45 678

    DECLARE dup_check CURSOR FAST_FORWARD

    FOR SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(companyphone , '(' , '') , ' ' ,

    '') , '-' , '') , ')' , '')

    , companyname

    , id

    FROM

    prospects

    OPEN dup_check

    FETCH NEXT FROM dup_check INTO @companyPhoneBeingChecked ,

    @companyNameBeingChecked , @IdBeingChecked

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE scan_rows CURSOR FAST_FORWARD

    FOR SELECT

    REPLACE(REPLACE(REPLACE(REPLACE(companyphone , '(' , '') ,

    ' ' , '') , '-' , '') , ')' , '')

    , companyname

    FROM

    prospects

    OPEN scan_rows

    FETCH NEXT FROM scan_rows INTO @companyPhonePossibleDup ,

    @companyNamePossibleDup

    WHILE @@FETCH_STATUS = 0

    BEGIN

    ----If records have the exact same companyphone value and companyphone is not empty (‘’)

    ----and companyphone is not null.

    IF ( @companyPhoneBeingChecked = @companyPhonePossibleDup )

    AND (

    @companyPhoneBeingChecked != ''

    or @companyPhoneBeingChecked != null

    )

    BEGIN

    ----duplicate records needs to become the same. For those records the following may be done:

    ----the highest value of a column within this duplicate set may be used for all duplicate prospects

    update

    prospects

    set

    companytype = (

    select

    max(companytype)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    companyemployees = (

    select

    max(companyemployees)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    companyvehicles = (

    select

    max(companyvehicles)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    companyname = (

    select

    max(companyname)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_sex = (

    select

    max(cp_sex)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_initials = (

    select

    max(cp_initials)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_firstname = (

    select

    max(cp_firstname)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_middlename = (

    select

    max(cp_middlename)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_lastname = (

    select

    max(cp_lastname)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_jobtitle = (

    select

    max(cp_jobtitle)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_email = (

    select

    max(cp_email)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_phone = (

    select

    max(cp_phone)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_phone2 = (

    select

    max(cp_phone2)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    cp_phonemobile = (

    select

    max(cp_phonemobile)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob = (

    select

    max(telco_mob)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob_connections = (

    select

    max(telco_mob_connections)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob_provider = (

    select

    max(telco_mob_provider)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob_dealer = (

    select

    max(telco_mob_dealer)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob_enddate = (

    select

    max(telco_mob_enddate)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_mob_costs = (

    select

    max(telco_mob_costs)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast = (

    select

    max(telco_vast)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast_connections = (

    select

    max(telco_vast_connections)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast_provider = (

    select

    max(telco_vast_provider)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast_dealer = (

    select

    max(telco_vast_dealer)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast_enddate = (

    select

    max(telco_vast_enddate)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_vast_costs = (

    select

    max(telco_vast_costs)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data = (

    select

    max(telco_data)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data_connections = (

    select

    max(telco_data_connections)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data_provider = (

    select

    max(telco_data_provider)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data_dealer = (

    select

    max(telco_data_dealer)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data_enddate = (

    select

    max(telco_data_enddate)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    telco_data_costs = (

    select

    max(telco_data_costs)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    importdate = (

    select

    max(importdate)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    filename = (

    select

    max(filename)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    presenter_interest = (

    select

    max(presenter_interest)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    presenter_budget = (

    select

    max(presenter_budget)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    campaignid_delme = (

    select

    max(campaignid_delme)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    update

    prospects

    set

    importcode = (

    select

    max(importcode)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    where

    companyphone = @companyPhonePossibleDup

    --Get the record with lowest id of this set of duplicates: record X.

    set @MinimumId = (

    select

    MIN(id)

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup

    )

    --Update all references of [prospectsincampaigns].prospectid that point to a record in setY to point to recordX

    update

    prospectsincampaigns

    set

    prospectid = @MinimumId

    where

    prospectid in (

    select

    id

    from

    prospects

    where

    companyphone = @companyPhonePossibleDup )

    -- in setY in [prospects], delete all duplicates except recordX

    delete from

    prospects

    where

    (

    companyphone = @companyPhonePossibleDup

    and id <> @MinimumId

    )

    END

    ElSE

    IF (

    @companyPhoneBeingChecked = ''

    or @companyPhoneBeingChecked = null

    )

    AND (

    lower(@companyNameBeingChecked) = lower(@companyNamePossibleDup)

    or upper(@companyNameBeingChecked) = upper(@companyNamePossibleDup)

    )

    BEGIN

    ----duplicate records needs to become the same. For those records the following may be done:

    ----the highest value of a column within this duplicate set may be used for all duplicate prospects

    update

    prospects

    set

    companytype = (

    select

    max(companytype)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    companyemployees = (

    select

    max(companyemployees)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    companyvehicles = (

    select

    max(companyvehicles)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_sex = (

    select

    max(cp_sex)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_initials = (

    select

    max(cp_initials)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_firstname = (

    select

    max(cp_firstname)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_middlename = (

    select

    max(cp_middlename)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_lastname = (

    select

    max(cp_lastname)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_jobtitle = (

    select

    max(cp_jobtitle)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_email = (

    select

    max(cp_email)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_phone = (

    select

    max(cp_phone)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_phone2 = (

    select

    max(cp_phone2)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    cp_phonemobile = (

    select

    max(cp_phonemobile)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob = (

    select

    max(telco_mob)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob_connections = (

    select

    max(telco_mob_connections)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob_provider = (

    select

    max(telco_mob_provider)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob_dealer = (

    select

    max(telco_mob_dealer)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob_enddate = (

    select

    max(telco_mob_enddate)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_mob_costs = (

    select

    max(telco_mob_costs)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast = (

    select

    max(telco_vast)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast_connections = (

    select

    max(telco_vast_connections)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast_provider = (

    select

    max(telco_vast_provider)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast_dealer = (

    select

    max(telco_vast_dealer)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast_enddate = (

    select

    max(telco_vast_enddate)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_vast_costs = (

    select

    max(telco_vast_costs)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data = (

    select

    max(telco_data)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data_connections = (

    select

    max(telco_data_connections)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data_provider = (

    select

    max(telco_data_provider)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data_dealer = (

    select

    max(telco_data_dealer)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data_enddate = (

    select

    max(telco_data_enddate)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    telco_data_costs = (

    select

    max(telco_data_costs)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    importdate = (

    select

    max(importdate)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    filename = (

    select

    max(filename)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    presenter_interest = (

    select

    max(presenter_interest)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    presenter_budget = (

    select

    max(presenter_budget)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    campaignid_delme = (

    select

    max(campaignid_delme)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    update

    prospects

    set

    importcode = (

    select

    max(importcode)

    from

    prospects

    where

    companyphone = @companyNamePossibleDup

    )

    where

    companyphone = @companyNamePossibleDup

    --Get the record with lowest id of this set of duplicates: record X.

    set @MinimumId = (

    select

    MIN(id)

    from

    prospects

    where

    companyname = @companyNamePossibleDup

    )

    --Update all references of [prospectsincampaigns].prospectid that point to a record in setY to point to recordX

    update

    prospectsincampaigns

    set

    prospectid = @MinimumId

    where

    prospectid in (

    select

    id

    from

    prospects

    where

    companyname = @companyNamePossibleDup )

    -- in setY in [prospects], delete all duplicates except recordX

    delete from

    prospects

    where

    (

    companyname = @companyNamePossibleDup

    and id <> @MinimumId

    )

    END

    FETCH NEXT FROM scan_rows INTO @companyPhonePossibleDup ,

    @companyNamePossibleDup

    END

    --delete records from [prospects] for which prospectsincampaigns.result=26 or prospectsincampaigns.result=13

    delete from

    prospects

    where

    id in ( select

    prospectid

    from

    ProspectsInCampaigns

    where

    result IN ( 26 , 13 ) )

    --delete records from [prospectsincampaigns] which have any of these resultcodes: 7,2,4,25,23,3,26,13

    delete from

    prospectsincampaigns

    where

    result IN ( 7 , 2 , 4 , 25 , 23 , 3 , 26 , 13 )

    CLOSE scan_rows

    DEALLOCATE scan_rows

    FETCH NEXT FROM dup_check INTO @companyPhoneBeingChecked ,

    @companyNameBeingChecked , @IdBeingChecked

    END

    CLOSE dup_check

    DEALLOCATE dup_check

  • November 10, 2011 at 9:35 am

    #1405874

    Is this a 1 time occurance?

    If so rewritting this might be very counter-productive.

    On such huge code I usually put a couple days aside for a rewrite... so unless you're asking for a paid consult, not really interested on my end.

  • November 10, 2011 at 9:41 am

    #1405884

    Seriously? Have you seen how many times you hit the "prospects" table every time your god-awful cursor runs?

    If I feel particularly masochistic this weekend, I might have a look. To do so, I'll need some sample data so please include some.

    But to be honest, what you need is a paid for consultant to come in and rewrite your query.

    EDIT

    Ninja's_RGR'us (11/10/2011)

    ... so unless you're asking for a paid consult, not really interested on my end.

    Snap 😀

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 10, 2011 at 10:16 am

    #1405914

    Well, I will give a few general suggestions you can use to help speed this monster up. It would take more than a couple of minutes to generate an ideal solution, but here are a few easy methods to speed it up.

    If you continue to use the CURSOR which is not ideal, then

    1) Add an index on "CompanyPhone".

    2) Instead of performing an UPDATE for each field 1 at a time, extract the "max" values into either (a) a set of variables using only one select statement OR (b) insert max records into a 1 record temp table. Then, perform 1 update statement using the output generated accordingly.

    3) Reset all variable values to a default value prior to the NEXT FETCH

    ---sample getting values into variables

    select companyType=max(companytype)

    , companyemployees = max(companyemployees)

    , etc....

    from prospects

    where companyphone = @companyPhonePossibleDup

    ---sample using variables

    update prospects

    set companytype= @companyType

    , companyemployees=@companyemployees

    , etc....

    where companyphone = @companyPhonePossibleDup

    --- reset all variables to a default value (null or blank)

    select @companyType=NULL, @companyemployees=NULL, etc....

  • November 10, 2011 at 10:40 am

    #1405925

    And if at all possible a little normalization would go a LONG way to making your life a lot easier. You need to spin off off several detail tables for these prospects. You are eating up huge amounts of resources and storage space with tons and tons of nulls. At least spin off a ProspectCampaign and ProspectAppointment tables. There looks like there could be 2-3 more as well.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • November 10, 2011 at 11:51 am

    #1405960

    Sean Lange (11/10/2011)

    And if at all possible a little normalization would go a LONG way to making your life a lot easier. You need to spin off off several detail tables for these prospects. You are eating up huge amounts of resources and storage space with tons and tons of nulls. At least spin off a ProspectCampaign and ProspectAppointment tables. There looks like there could be 2-3 more as well.

    [appointmentdate100] [date] NULL

    For his sake I hope Celko doesn't see this...:w00t:

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply