I have four Excel tables named BasicData, FacilityData, StaffData and StudentData. They all have SchoolCode as PK in them
if I follow the solution which you have suggested here then I will have to add a similar Primary key to the other three tables too when I'm exporting excel data to SQL Server?.
Also Will I be able to extract data from all the tables in the database based on this new primary key?
SchoolCode is you natural Key do not confuse it will the primary key.
Following might be the structure of your files (purely assumtions)
1) BasicData which represent the Schools, main table you can also call it Master table.
2) following table FacilityData, StaffData and StudentData are considered as Child table because all will have SchoolCode in them.
You can join these tables on SchoolCode anytime from the basicData table to get the id.
you are focusing on the solution instead of fixing the actual issue. which is SchoolCode is getting duplicated in you basicData (Excel sheet) as you have mentioned that SchoolCode is you Primary key. which is clearly not in this case.
The above solution is just to give you can idea that if you change your primary key, you will be able to insert records in BasicData.
but this will not fix your actual problem. consider the following scenario
Declare @BasicData TABLE
BasicData INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,School_code nvarchar(255) NOT NULL
Declare @StudentData Table
School_code nvarchar(255) NOT NULL
, Student_code nvarchar(255) NOT NULL
, Student_Name nvarchar(255) NOT NULL
------ If there are duplicates in the Basicdata table
insert into @BasicData
(District_name, School_code, School_name)
select 'ABC', '111', 'ABC School' union all
select 'ABC', '555', 'DDD School' union all
select 'ABC', '111', 'XYZ School'
----- @StudentData is a Child table so that SchoolCode can be repeated as there can be many student in a school.
insert into @StudentData
select '555', 'Student001', 'Student A' union all
select '111', 'Student002', 'Student B' union all
select '111', 'Student003', 'Student C'
------------ now Student B & C both represent two different school. this issue is appearing because you have duplicates in your BasicData.
select b.School_code, b.School_name, f.Student_Name
from @BasicData b
join @StudentData f on b.School_code = f.School_code
Hope it helps