In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
WHERE LKC.seq = 2
**Note the tables of [LockerPopulation] and TST.dbo.School are only used for the update statement to obtain the data.

A normal select statement looks like the following:
select * from  TST.dbo.Locker LKR 
JOIN TST.dbo.Lock LK ON LKR.lockID = LK.lockID
JOIN TST.dbo.LockCombination LKC ON LK.lockID = LKC.lockID
where  LKR.number in (000,001,1237)

In case you need the ddl statements for the tables affected here are the ddl statements:

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
-------------------
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
--------------------
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]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[LockCombination]  WITH NOCHECK ADD  CONSTRAINT [FK_LockCombination_Lock] FOREIGN KEY([lockID])
REFERENCES [dbo].[Lock] ([lockID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[LockCombination] CHECK CONSTRAINT [FK_LockCombination_Lock]
GO

• Edited by wendy elizabeth 2 hours 44 minutes ago

A little sample of what you're trying to achieve might help.

If there are no rows in LKC for seq 2 to 5, you aren't going to be able to do an update on them, you'll have to have inserted them first - how to insert them would be dependent on your requirements for these

----Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

create table #t (id int)

create table #seq (id int not null identity(1,1))

insert into #seq default values
go 10

insert into #t values (1),(3),(5)


insert into #t select id from #seq
where not exists (select * from #t where #t.id=#seq.id)

select * from #t order by id

Hello Wendy - You can achieve this by using MERGE UPDATE method so essentially using MERGE feature you can compare the outcome of JOIN operation and execute UPDATE and for the ones that are not matched, you can issue INSERT. Have a look at the sample below, here rows from tblEmployee1 (Target) are matched with tblEmployee2 (Source) and appropriate action is taken i.e. if found then update salary or else insert the record

 

-- tblEmployee1 contains 2 rows: 1, 1000 & 2, 2000
select * from tblEmployee1
-- tblEmployee2 contains 2 rows: 1, 10000 & 3, 3000
select * from tblEmployee2

-- Merge Update Example
-- At the end of the execution, EmpID 1 will have updated salary to 10,000 and
-- a new row with value 3, 3000 will also be inserted in tblEmployee1
MERGE tblEmployee1 AS T
USING tblEmployee2 AS S
ON (T.EmpID = S.EmpID) 
WHEN NOT MATCHED
    THEN INSERT(EmpID, Salary) VALUES(S.EmpID, S.Salary)
WHEN MATCHED 
    THEN UPDATE SET T.Salary = S.Salary;

Hope you can use this to address your requirement !

Sounds like what you need is to generate missing records based on sequence number. That being the case you should be using a number table and do a left join with it based on seq value to get missing records. Then you can populate some default values for the other columns of these missing records as per your requirement.