October 5, 2016 at 8:58 pm
In t-sql 2012, I have displayed 3 ddls for 3 tables that I want rows to be updated. Basically in the Locker table, I am going to update the data for preexisting data. However I want the lockID value in the locker table to be the same value in the Lock
and the LockCombination tables. Basically I will create rows in the locker table. Once the locker table is updated, I want the lockid value to refer to the same lockID value between the locker, lock, and LockCombination tables. Thus would you
show me the sql onhow to accomplish this goal?
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[locationID] [int] NULL,
[grade] [varchar](4) NULL,
[reserved] [bit] NULL,
[lockID] [int] NULL,
[share] [bit] NULL,
[lockType] [varchar](3) NULL,
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
(
[lockerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_Lock]
GO
ALTER TABLE [dbo].[Locker] WITH CHECK ADD CONSTRAINT [FK_Locker_LockerLocation] FOREIGN KEY([locationID])
REFERENCES [dbo].[LockerLocation] ([locationID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_LockerLocation]
GO
ALTER TABLE [dbo].[Locker] WITH NOCHECK ADD CONSTRAINT [FK_Locker_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Locker] CHECK CONSTRAINT [FK_Locker_School]
GO
--------------
CREATE TABLE [dbo].[Lock](
[lockID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[schoolID] [int] NOT NULL,
[serialNumber] [varchar](20) NULL,
[type] [varchar](3) NULL,
[comboSeq] [tinyint] NOT NULL,
CONSTRAINT [PK_Lock] PRIMARY KEY NONCLUSTERED
(
[lockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Lock] WITH NOCHECK ADD CONSTRAINT [FK_Lock_School] FOREIGN KEY([schoolID])
REFERENCES [dbo].[School] ([schoolID])
GO
ALTER TABLE [dbo].[Lock] CHECK CONSTRAINT [FK_Lock_School]
GO
-------------------
CREATE TABLE [dbo].[LockCombination](
[comboID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[lockID] [int] NOT NULL,
[seq] [tinyint] NOT NULL,
[combo] [varchar](8) NOT NULL,
CONSTRAINT [PK_LockCombination] PRIMARY KEY NONCLUSTERED
(
[comboID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 97) ON [PRIMARY]
) ON [PRIMARY]
October 6, 2016 at 2:06 am
"However I want the lockID value in the locker table to be the same value in the Lock
and the LockCombination tables. Basically I will create rows in the locker table. Once the locker table is updated, I want the lockid value to refer to the same lockID value between the locker, lock, and LockCombination tables. "
This is to me a mighty strange way of perceiving a set of related tables, but if it works for you and you understand the relationships between the tables, then so be it.
A locker has a lock, which has a serial number on it. The lock can be changed if it's broken or worn out; thrown away and replaced with another one from the big box of locks. A row in the locker table holds the PK of the current lock in the LockID column.
The locks are of the numeric combination type, and the combination can be changed. Every combination used with every lock is recorded in the LockCombination table, including the current combination. A row in the LockCombination table holds the PK of the lock it belongs to, and the iteration of the combination (the combination on the lock when new will be 1, and will increment with each change).
Scenario 1.
If new lockers are prefitted with a locks and you are planning to add a row to the locker table (installing a locker in a school), then you need to know which lock has been fitted. You can identify a lock by serialNumber and obtain the LockID from the locks table, creating a new row if a lock with this serialNumber doesn't already exist. You will also need to know the current combination of this lock to check that it exists in the LockCombination table along with the relevant LockID, and create it if it doesn't already exist.
What other scenarios can you anticipate?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2016 at 7:59 am
This is only a one time update. I am sorry I did not mention that earlier. I basically just need to place existing data into the tables for the current school year fore a particular school. Someone ran a script and it made the data incorrect. I just need a way to make the data correct.
October 6, 2016 at 8:03 am
wendy elizabeth (10/6/2016)
This is only a one time update. I am sorry I did not mention that earlier. I basically just need to place existing data into the tables for the current school year fore a particular school. Someone ran a script and it made the data incorrect. I just need a way to make the data correct.
What do you have in the way of "correct data"?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 6, 2016 at 10:37 am
I have this school years data and I am making a copy of it for the previous school year. Basically the schoolid for this current school year changed from last school year. That is why this data is needed.
October 6, 2016 at 10:57 am
wendy elizabeth (10/6/2016)
I have this school years data and I am making a copy of it for the previous school year. Basically the schoolid for this current school year changed from last school year. That is why this data is needed.
It's much easier than you expect. Since school year is not recorded in any of these tables (no dates are recorded), simply change the old schoolid to the new schoolid in the two tables where it is stored.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 6, 2016 at 2:50 pm
The school year is used. These 3 tables are joined to the following tables to determine what the school year is.
Note: I did not list the following 2 tables originally since I am not changing the data on those 2 tables:
CREATE TABLE [dbo].[School](
[schoolID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[districtID] [int] NOT NULL,
[ncesSchoolID] [varchar](9) NULL,
[number] [varchar](7) NOT NULL,
[name] [varchar](50) NOT NULL,
[type] [varchar](5) NULL,
[comments] [varchar](255) NULL,
[address] [varchar](50) NULL,
[city] [varchar](25) NULL,
[state] [varchar](2) NULL,
[zip] [varchar](10) NULL,
[phone] [varchar](25) NULL,
[fax] [varchar](25) NULL,
[varchar](100) NULL,
[dualEnrollment] [bit] NULL,
[title1] [varchar](3) NULL,
[principalName] [varchar](154) NULL,
[principalTitle] [varchar](50) NULL,
[principalEmail] [varchar](100) NULL,
[schoolGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[catalogID] [int] NULL,
[satNumber] [varchar](8) NULL,
[standardCode] [varchar](10) NULL,
[agency] [varchar](50) NULL,
[restructureStartDate] [smalldatetime] NULL,
[restructureEndDate] [smalldatetime] NULL,
[groupNumber] [varchar](10) NULL,
[aypGrouping] [varchar](10) NULL,
[reportingSchoolID] [int] NULL,
[physicalAddress] [varchar](50) NULL,
[physicalCity] [varchar](25) NULL,
[physicalState] [varchar](2) NULL,
[physicalZip] [varchar](10) NULL,
[county] [varchar](50) NULL,
[status] [varchar](8) NULL,
[accreditation] [varchar](10) NULL,
[gradeType] [varchar](5) NULL,
[sector] [varchar](10) NULL,
[locale] [varchar](2) NULL,
[locale2] [varchar](2) NULL,
[blueRibbon] [varchar](1) NULL,
[title1distinguished] [varchar](1) NULL,
[entityID] [varchar](12) NULL,
[aypStatus] [varchar](8) NULL,
[varchar](2) NULL,
[sessionType] [varchar](4) NULL,
[legacyKey] [varchar](50) NULL,
[fRYSCServiceCenterCode] [varchar](15) NULL,
[modifiedByID] [int] NULL,
[modifiedDate] [smalldatetime] NULL,
[typeBIE] [varchar](3) NULL,
[agencyBIE] [varchar](6) NULL,
[schoolLevel] [varchar](4) NULL,
[stateClassification] [varchar](2) NULl)
CREATE TABLE [dbo].[Calendar](
[calendarID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[districtID] [int] NOT NULL,
[schoolID] [int] NOT NULL,
[endYear] [smallint] NOT NULL,
[name] [varchar](30) NOT NULL,
[number] [varchar](3) NULL,
[startDate] [smalldatetime] NULL,
[endDate] [smalldatetime] NULL,
[comments] [varchar](255) NULL,
[exclude] [bit] NULL,
[summerSchool] [bit] NULL,
[studentDay] [smallint] NULL,
[teacherDay] [smallint] NULL,
[wholeDayAbsence] [smallint] NULL,
[halfDayAbsence] [smallint] NULL,
[calendarGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[alternativeCode] [varchar](1) NULL,
[title3] [bit] NULL,
[title3consortium] [varchar](50) NULL,
[title1] [varchar](2) NULL,
[legacyKey] [int] NULL,
[schoolChoice] [bit] NULL,
[type] [varchar](4) NULL,
[countDate] [smalldatetime] NULL,
[assignmentRequired] [bit] NULL,
[legacyKey2] [varchar](50) NULL,
[sifExclude] [bit] NOT NULL,
[positiveAttendanceEnabled] [bit] NOT NULL,
[positiveAttendanceEditDays] [int] NOT NULL,
[track] [varchar](2) NULL,
[typeBIE] [varchar](2) NULL,
[sequence] [smallint] NULL,
[externalLMSExclude] [bit] NOT NULL,
October 7, 2016 at 3:34 am
wendy elizabeth (10/6/2016)
The school year is used. These 3 tables are joined to the following tables to determine what the school year is.Note: I did not list the following 2 tables originally since I am not changing the data on those 2 tables:
CREATE TABLE [dbo].[School](
[schoolID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
.....
This doesn't change anything, Wendy. The three locker tables don't record school year anywhere - there's a data set (of lockers, their locks, and the combination of the locks) per school, not per school year. If you join the locker tables to these two tables above, you will get the same set of locker table details duplicated for each year per school.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2016 at 8:55 am
Since the data is duplicated for each school per school year, you are just saying that I should create duplicate rows for the previous school year and change the schoolid, correct?
My problem is that the tables are linked to each other with primary and foreign keys. There are also constraints between the tables. I can not just created duplicate rows in the corresponding tables. Wouldn't this be affected?
Also the primary keys to each table uniquely create numbers. Wouldn't this cause some kind of a problem?
Thank you very much for your assistance!
October 7, 2016 at 9:13 am
wendy elizabeth (10/7/2016)
Since the data is duplicated for each school per school year,...
I didn't say that. I said that if you query for any school year, you will get the same locker data for each year.
...you are just saying that I should create duplicate rows for the previous school year and change the schoolid, correct?
No, I'm saying all you have to do is change the old schoolid to the new schooled in the locker tables.
My problem is that the tables are linked to each other with primary and foreign keys. There are also constraints between the tables. I can not just created duplicate rows in the corresponding tables. Wouldn't this be affected?
It's not a problem, it's by design. You have to understand the design and work with it.
Also the primary keys to each table uniquely create numbers. Wouldn't this cause some kind of a problem?
Only if you do something you are not supposed to 😉
Thank you very much for your assistance!
No problem. Please spend a little time on the ERD for this system before you proceed with any changes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2016 at 9:28 am
Isn't this as simple as
School--(1,M)--Locker--(1,M)--LockerAssignment--(M,1)---Student
and
LockerAssignment--(M,1)--SchoolYear
From that you can join the tables and get all the current locker assignments etc.
October 7, 2016 at 9:32 am
pietlinden (10/7/2016)
Isn't this as simple asSchool--(1,M)--Locker--(1,M)--LockerAssignment--(M,1)---Student
and
LockerAssignment--(M,1)--SchoolYear
From that you can join the tables and get all the current locker assignments etc.
I guess it could be, if there's a LockerAssignment table - which there isn't, yet...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 7, 2016 at 4:00 pm
Would that make it too easy? (StudentID, LockerID, StartLease, EndLease) or similar?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply