January 1, 2013 at 6:26 am
HI,
How to find duplicate records as below query
Select A.SS_ID,A.SD_ID, A.SD_Info, A.SD_User, A.SD_Date
from dbo.BC_ShiftSummaryInfo A
Join dbo.BC_ShiftSummary B
On A.SS_ID = B.SS_ID
Where B.ShiftStartDate > '2012-12-14 00:00:00.000'
and B.ShiftStartDate <'2012-12-15 00:00:00.00' order by B.ShiftStartDate
--8400 Total Record count in dbo.BC_ShiftSummaryInfo as per date range
-- Actual Records is 5023 should come above date range.
Table structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BC_ShiftSummaryInfo](
[SS_ID] [uniqueidentifier] NOT NULL,
[SD_ID] [uniqueidentifier] NOT NULL,
[SD_Info] [ntext] NOT NULL,
[SD_User] [nvarchar](400) NULL,
[SD_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[BC_ShiftSummaryInfo] WITH NOCHECK ADD CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display] FOREIGN KEY([SD_ID])
REFERENCES [dbo].[BC_Display] ([SD_ID])
GO
ALTER TABLE [dbo].[BC_ShiftSummaryInfo] CHECK CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display]
GO
I want remove duplicate records 3377 in BC_ShiftSummaryInfo table. Please provide the script how to delete duplicate records
thanks
ananda
January 1, 2013 at 7:29 am
I think you have the DDL you posted mixed up with what you are after. The FK references BC_Display. Can you please post the table definition for both BC_ShiftSummary and BC_ShiftSummaryInfo, and only the FKs related between those two tables?
Also, if you were to find multiple rows in BC_ShiftSummaryInfo for the same SS_ID which row should be kept? i.e. what is the criteria? newest per SD_Date?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 1, 2013 at 9:12 pm
sorry for late reply..
export data into BC_ShiftSummaryInfo table some duplicate data was inserted during that time period.. I want delete 3377 duplicate records on BC_ShiftSummaryInfo
Table structure - [BC_ShiftSummaryInfo]
CREATE TABLE [dbo].[BC_ShiftSummaryInfo](
[SS_ID] [uniqueidentifier] NOT NULL,
[SD_ID] [uniqueidentifier] NOT NULL,
[SD_Info] [ntext] NOT NULL,
[SD_User] [nvarchar](400) NULL,
[SD_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[BC_ShiftSummaryInfo] WITH NOCHECK ADD CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display] FOREIGN KEY([SD_ID])
REFERENCES [dbo].[BC_Display] ([SD_ID])
GO
ALTER TABLE [dbo].[BC_ShiftSummaryInfo] CHECK CONSTRAINT [FK_BC_ShiftSummaryInfo_BC_Display]
GO
Table structure - [BC_ShiftSummary]
CREATE TABLE [dbo].[BC_ShiftSummary](
[SS_ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[SC_Name] [nvarchar](64) NOT NULL,
[SC_OwnerID] [varchar](50) NOT NULL,
[CreatedBy] [nvarchar](255) NOT NULL,
[CreateDate] [datetime] NOT NULL,
[Status] [tinyint] NOT NULL,
[SubmitDate] [datetime] NULL,
[SubmittedBy] [nvarchar](255) NULL,
[ApproveDate] [datetime] NULL,
[ApprovedBy] [nvarchar](255) NULL,
[Eq_No] [int] NOT NULL,
[ShiftID] [nvarchar](16) NOT NULL,
[ShiftName] [nvarchar](16) NOT NULL,
[ShiftStartDate] [datetime] NOT NULL,
[ShiftEndDate] [datetime] NOT NULL,
[MinTillLocked] [int] NULL,
[Archive_File_name] [nvarchar](55) NULL,
[TakeOverDate] [datetime] NULL,
[TakeOverBy] [nvarchar](255) NULL,
[TakeOverRole] [nvarchar](255) NULL,
CONSTRAINT [PK_BC_ShiftSummary] PRIMARY KEY CLUSTERED
(
[SS_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
ALTER TABLE [dbo].[BC_ShiftSummary] ADD CONSTRAINT [DF_BC_ShiftSummary_SS_ID] DEFAULT (newid()) FOR [SS_ID]
GO
ALTER TABLE [dbo].[BC_ShiftSummary] ADD CONSTRAINT [DF_BC_ShiftSummary_CreateDate] DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[BC_ShiftSummary] ADD DEFAULT (NULL) FOR [Archive_File_name]
GO
Table structure - [BC_Display]
CREATE TABLE [dbo].[BC_Display](
[SD_ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[LKP_Type] [varchar](50) NOT NULL,
[Name] [nvarchar](64) NOT NULL,
[OwnerID] [varchar](50) NOT NULL,
[Caption] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[Height] [int] NOT NULL,
[LKP_Unit] [varchar](50) NOT NULL,
[Scrolling] [bit] NOT NULL,
[DDAO_ID] [uniqueidentifier] NULL,
[RO] [tinyint] NOT NULL,
[DDAO_Name] [nvarchar](64) NULL,
[DDAO_DataSetView] [nvarchar](64) NULL,
[DDAO_TableOperation] [nvarchar](64) NULL,
[Query_ConnApp] [nvarchar](64) NULL,
[Query_ConnSource] [nvarchar](64) NULL,
[Query_ConnKey] [nvarchar](64) NULL,
[Query] [ntext] NULL,
[GOTO_WKSP] [varchar](256) NULL,
[GOTO_WKBK] [varchar](256) NULL,
[GOTO_WKSH] [varchar](256) NULL,
[GOTO_URL] [ntext] NULL,
[TagGroup_Type] [varchar](50) NULL,
CONSTRAINT [SN_DISPLAY_PK] PRIMARY KEY CLUSTERED
(
[SD_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] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[BC_Display] WITH NOCHECK ADD CONSTRAINT [FK_BC_Display_BC_Owner] FOREIGN KEY([OwnerID])
REFERENCES [dbo].[BC_Owner] ([OwnerID])
GO
ALTER TABLE [dbo].[BC_Display] CHECK CONSTRAINT [FK_BC_Display_BC_Owner]
GO
ALTER TABLE [dbo].[BC_Display] ADD CONSTRAINT [DF_BC_Display_SD_ID] DEFAULT (newid()) FOR [SD_ID]
GO
January 1, 2013 at 11:08 pm
Duplicate post :alien: please post reply here http://www.sqlservercentral.com/Forums/Topic1401550-392-1.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply