Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Remove duplicate records? Expand / Collapse
Author
Message
Posted Tuesday, January 1, 2013 6:26 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:00 AM
Points: 1,054, Visits: 2,974
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
Post #1401590
Posted Tuesday, January 1, 2013 7:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:38 PM
Points: 7,097, Visits: 12,598
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
Post #1401598
Posted Tuesday, January 1, 2013 9:12 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:00 AM
Points: 1,054, Visits: 2,974
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,
[URL] [ntext] 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

Post #1401644
Posted Tuesday, January 1, 2013 11:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:26 AM
Points: 2,840, Visits: 3,963
Duplicate post 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
Post #1401658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse