Here's one way in which it could be done:
USE [TestBed]
GO
/****** Object: Table [dbo].[Duplicates] Script Date: 02/06/2008 10:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Duplicates](
[EmpId] [int] NOT NULL,
[EmpName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[DeptId] [int] NOT NULL,
[DeptName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Sal] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[DuplicatesCSV] Script Date: 02/06/2008 10:22:30 ******/
CREATE TABLE [dbo].[DuplicatesCSV](
[EmpId] [int] NOT NULL,
[EmpName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[DeptId] [int] NOT NULL,
[DeptName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[SalCSV] [nvarchar](200) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
USE [TestBed]
GO
/****** Object: Index [IX_Duplicates] Script Date: 02/06/2008 10:26:18 ******/
CREATE CLUSTERED INDEX [IX_Duplicates] ON [dbo].[Duplicates]
(
[EmpId] ASC,
[DeptId] ASC,
[DeptName] ASC,
[EmpName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
/****** Object: Index [IX_DuplicatesCSV] Script Date: 02/06/2008 10:26:18 ******/
CREATE CLUSTERED INDEX [IX_DuplicatesCSV] ON [dbo].[DuplicatesCSV]
(
[EmpId] ASC,
[DeptId] ASC,
[DeptName] ASC,
[EmpName] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO
/****** Object: UserDefinedFunction [dbo].[fselSalList] Script Date: 02/06/2008 10:23:59 ******/
CREATE function [dbo].[fselSalList]
(
@empId int,
@empName varchar(50),
@deptId int,
@deptName varchar(50)
)
returns varchar(200)
as
begin
declare @list varchar(200)
select @list = COALESCE(@list + ',', '') + Cast(sal As varchar(10))
from Duplicates
where EmpId = @empId and EmpName = @empName and DeptId = @deptId and DeptName = @deptName
return @list
end
GO
/*
-- to initially load duplicates tables (should Duplicates table already exist)
insert into DuplicatesCSV(EmpId, EmpName, DeptId, DeptName, SalCSV)
select distinct EmpId, EmpName, DeptId, DeptName, dbo.fselSalList(EmpId, EmpName, DeptId, DeptName)
from Duplicates
*/
GO
CREATE TRIGGER [iuDuplicates]
ON [dbo].[Duplicates]
FOR INSERT, UPDATE
AS
BEGIN
set nocount on
declare @empId int, @empName varchar(20), @deptId int, @DeptName varchar(20)
select @empId = EmpId, @empName = EmpName, @deptId = DeptId, @deptName = DeptName
from inserted
delete from DuplicatesCSV
where EmpId = @empId
and EmpName = @empName
and DeptId = @deptId
and DeptName = @deptName
insert into DuplicatesCSV(EmpId, EmpName, DeptId, DeptName, SalCSV)
select distinct EmpId, EmpName, DeptId, DeptName, dbo.fselSalList(EmpId, EmpName, DeptId, DeptName)
from inserted
END
GO
/****** Object: Trigger [dDuplicates] Script Date: 02/06/2008 10:27:36 ******/
CREATE TRIGGER [dDuplicates]
ON [dbo].[Duplicates]
FOR DELETE
AS
BEGIN
set nocount on
declare @empId int, @empName varchar(20), @deptId int, @DeptName varchar(20)
select @empId = EmpId, @empName = EmpName, @deptId = DeptId, @deptName = DeptName
from deleted
delete from DuplicatesCSV
where EmpId = @empId
and EmpName = @empName
and DeptId = @deptId
and DeptName = @deptName
if exists
(
select EmpId
from Duplicates
where EmpId = @empId
and EmpName = @empName
and DeptId = @deptId
and DeptName = @deptName
)
begin
insert into DuplicatesCSV(EmpId, EmpName, DeptId, DeptName, SalCSV)
select distinct EmpId, EmpName, DeptId, DeptName, dbo.fselSalList(EmpId, EmpName, DeptId, DeptName)
from deleted
end
END
I'm sure that there are others... but this works on a small dataset .. not sure how 'efficient' it would be for 25,000 records. Try it out on a test database 'copy'.
Joe
Enter your message below
Sign in or Join us (it's free).