SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove duplicate records?


Remove duplicate records?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6951 Visits: 3501
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
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40430 Visits: 14413
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
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6951 Visits: 3501
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


Bhuvnesh
Bhuvnesh
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13502 Visits: 4077
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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search