Duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicates

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-09 : 15:23:07

Hi I am trying to find all the duplicates in the School_tbl because from what I see there is allot how would I go about dong that
and then once I find them how do I delte them all at once

SELECT COUNT([Student ID]) AS StudentIDSchool, [School Name], [School Phone], [School Address], [School City], [School State], [School Zip]FROM School_tblGROUP BY [School Name], [School Phone], [School Address], [School City], [School State], [School Zip]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 15:31:22

First have a backup then maybe delete some records.

What is [Student ID]?

No, you're never too old to Yak'n'Roll if you're too young to die.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-09 : 15:33:07

the [Student ID] is the foreign key in the School_tbl

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-09 : 15:39:08

Why a foreign key to a student?
A student should have a foreign key to a school or am i wrong?

I am asking that because I have a suspicion that there are many duplicates because the relation is wrong...

No, you're never too old to Yak'n'Roll if you're too young to die.

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-09 : 15:52:32

Ok see at first the Student_tbl and the School_tbl were all one table, but I split them up so that when the student changes school they will have a record of it plus the history. I want to get rid of the school information which is in the Student Table. the Primary in the Student_tbl is [Student ID]. Before I ran them togehter I ran this statemtent so that I could create the join

Alter Table [School_tbl] with nocheck add constraint [FK_School_tbl_Student_ind] foreign key ([Student ID])
REFERENCES [Student_ind]([Student ID])

here is the School_tbl

CREATE TABLE [dbo].[School_tbl](
	[Family ID] [nvarchar](50) NULL,
	[Parent ID] [nvarchar](50) NULL,
	[Beginning Time] [nvarchar](50) NULL,
	[Ending Time] [nvarchar](50) NULL,
	[Student ID] [nvarchar](50) NULL,
	[School Name] [nvarchar](50) NULL,
	[School Phone] [nvarchar](50) NULL,
	[School Address] [nvarchar](50) NULL,
	[School City] [nvarchar](50) NULL,
	[School State] [nvarchar](50) NULL,
	[School Zip] [nvarchar](50) NULL,
	[Time School Starts] [datetime] NULL,
	[Time School Ends] [datetime] NULL,
	[Student GPA] [nvarchar](50) NULL,
	[Grade] [nvarchar](50) NULL,
	[schoolID] [nvarchar](255) NULL,
	[Studentschool Id] [nvarchar](255) NULL,
	[StudentRegistration] [nvarchar](255) NULL,
	[StudentRelease] [nvarchar](255) NULL,
	[ParentRelease] [nvarchar](255) NULL,
	[Students Released] [datetime] NULL,
	[Reason] [nvarchar](255) NULL,
	[Event Key field] [int] NULL,
	[SchID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_School_tbl] PRIMARY KEY CLUSTERED 
(
	[SchID] 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

and here is the student_tbl

CREATE TABLE [dbo].[Student_ind](
	[Student ID] [nvarchar](50) NOT NULL,
	[Family ID] [nvarchar](50) NULL,
	[Parent ID] [nvarchar](50) NULL,
	[Referral Location] [nvarchar](50) NULL,
	[Referral Date] [datetime] NULL,
	[Registration Date] [datetime] NULL,
	[Student First Name] [nvarchar](50) NULL,
	[Student Last Name] [nvarchar](50) NULL,
	[Student SS#] [nvarchar](50) NULL,
	[Student Sex] [nvarchar](50) NULL,
	[Grade] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[Student Date of Birth] [datetime] NULL,
	[School Name] [nvarchar](50) NULL,
	[School Phone] [nvarchar](50) NULL,
	[School Address] [nvarchar](50) NULL,
	[School City] [nvarchar](50) NULL,
	[School State] [nvarchar](50) NULL,
	[School Zip] [nvarchar](50) NULL,
	[Time School Starts] [datetime] NULL,
	[Time School Ends] [datetime] NULL,
	[Tribal Affiation] [nvarchar](50) NULL,
	[Student GPA] [nvarchar](50) NULL,
	[Culture] [bit] NOT NULL,
	[Education] [bit] NOT NULL,
	[Youth Development] [bit] NOT NULL,
	[Sports & Fitness] [bit] NOT NULL,
	[Technology] [bit] NOT NULL,
	[Event ID] [nvarchar](50) NULL,
	[TANFreferral] [nvarchar](255) NULL,
	[Enrollmentstatus] [nvarchar](255) NULL,
	[StudentRegistration] [nvarchar](255) NULL,
	[StudentRelease] [nvarchar](255) NULL,
	[ParentRelease] [nvarchar](255) NULL,
	[Students Released] [datetime] NULL,
	[Reason] [nvarchar](255) NULL,
	[Studentschool Id] [nvarchar](255) NULL,
	[schoolID] [nvarchar](255) NULL,
	[Scanned Documents] [ntext] NULL CONSTRAINT [DF_Student_ind_Scanned Documents]  DEFAULT (N'Scanned Documents'),
 CONSTRAINT [PK_Student_ind] PRIMARY KEY CLUSTERED 
(
	[Student 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]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources